ROWNUM pseudo-column

The ROWNUM pseudo-column assigns a unique number to each row in the query result set. The value of ROWNUM indicates the position of the row in the result set. For example, the first row is assigned the value 1, the second row is assigned the value 2, and so on. This topic describes how to use the ROWNUM pseudo-column.

You can use the ROWNUM pseudo-column to limit the number of rows returned by a query. For example, the following statement returns the first five rows from the employees table:

SELECT * FROM employees WHERE rownum <=5;

When you use the ROWNUM pseudo-column, note the following:

• If you specify an ORDER BY clause after ROWNUM, the query result set is sorted based on the ORDER BY clause. If you embed the ORDER BY clause in a subquery and specify the ROWNUM pseudo-column in the outer query, the ROWNUM condition is applied after the rows are sorted. For example, the following statement returns the five oldest employees in the employees table:

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

  The following statement correctly returns the five oldest employees in the employees table:

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

• If you specify ROWNUM greater than any positive integer in the WHERE clause, the query returns no rows. For example, the following statement returns no rows:

  SELECT * FROM employees WHERE rownum > 1;
  

  When the query retrieves the first row from the employees table, the ROWNUM pseudo-column is assigned the value 1. The WHERE condition evaluates to FALSE, so this row is excluded from the result set. When the query retrieves the second row, the ROWNUM pseudo-column is assigned the value 1 again. The WHERE condition evaluates to FALSE, so this row is also excluded from the result set. This process continues for all rows in the table. Therefore, no rows are returned.

You can use the UPDATE statement to assign the ROWNUM pseudo-column value to a column in a table. For example:

UPDATE employees SET id = rownum;

This statement assigns the ROWNUM pseudo-column value to the id column in the employees table. The id column is assigned the values 1, 2, 3, and so on, until all rows in the table are processed.