ROWNUM Pseudocolumn
For each row returned by a query, the ROWNUM
pseudocolumn returns a number indicating the order in which the row was selected. The first row selected has a ROWNUM
of 1, the second a ROWNUM
of 2, and so on.
Use ROWNUM
to limit the number of rows returned by a query as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;
The order in which rows are selected depends on the index used and the join order. If you specify an ORDER BY
clause, ROWNUM
is assigned before sorting. However, the presence of the ORDER BY
clause may change the index used and the join order. If the order of selected rows changes, the ROWNUM
value associated with each selected row could also change.
For example, the following query may return a different set of employees than the preceding query if a different index is used:
SELECT * FROM employees WHERE ROWNUM < 10 ORDER BY last_name;
Conditions testing for ROWNUM
values greater than a positive integer are always false. For example, the following query returns no rows:
SELECT * FROM employees WHERE ROWNUM > 1;
Use ROWNUM
to assign unique values to each row of a table. For example:
UPDATE my_table SET column1 = ROWNUM;
If your query contains either FIRST
NumRows
or ROWS
m
TO
n
, do not use ROWNUM
to restrict the number of rows returned. For example, the following query results in an error message:
SELECT FIRST 2 * FROM employees WHERE ROWNUM <1 ORDER BY employee_id; 2974: Using rownum to restrict number of rows returned cannot be combined with first N or rows M to N