ROWNUM Pseudocolumn

The ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query.

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT *
 FROM employees
 WHERE ROWNUM < 11;

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. 

For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement does not necessarily return the same rows as the preceding example:

SELECT *
 FROM employees
 WHERE ROWNUM < 11
 ORDER BY last_name;

Conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT *
 FROM employees
 WHERE ROWNUM > 1;