ROWNUM pseudocolumn

The ROWNUM pseudocolumn assigns a number to each row returned by a query, which indicates the position of the row in the result set. It returns 1 for the first row, 2 for the second row, and so forth. This topic describes how to use the ROWNUM pseudocolumn.

ROWNUM can limit the number of rows returned by a query. For example, the following statement returns five rows in the employees table.

SELECT * FROM employees WHERE rownum <=5;

Take note of the following considerations when you use ROWNUM:

• If an ORDER BY clause follows ROWNUM in a query, the rows that meet the WHERE condition are reordered. If you put an ORDER BY clause in a subquery and place the ROWNUM condition in a top-N query, you can force the ROWNUM condition to be executed after the ordering of the rows. For example, the following SQL statement queries all employees and reorders the information of the first five employees in the result set by age in descending order:

  SELECT * FROM employees WHERE rownum <=5 ORDER BY age DESC;
  

  To query information of the five oldest employees in the employees table, execute the following SQL statement:

  SELECT * FROM (SELECT * FROM employees ORDER BY age DESC) WHERE rownum <= 5;
  

• If you specify that ROWNUM in the WHERE clause is greater than any positive integer, FALSE is always returned. For example, if you execute the following SQL statement, no result is returned:

  SELECT * FROM employees WHERE rownum > 1;
  

  The ROWNUM value 1 is assigned to the first row fetched, which makes the WHERE condition FALSE. Therefore, this row is not returned. The ROWNUM value 1 is then assigned to the second row to be fetched, and the WHERE condition is still FALSE. This row is again not returned. All rows subsequently fail to meet the condition, so no data is returned.

You can use the UPDATE statement to assign the ROWNUM values to each row in a column of the table. Here is an example:

UPDATE employees SET ID = rownum;

This statement assigns the ROWNUM values from 1 to N to the rows in the ID column of the employees table.