ROWNUM and ORDER BY in Oracle PL/SQL

From the Oracle7 Server SQL Reference Manual: ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

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

SELECT * FROM emp WHERE ROWNUM < 10;

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx SET col1 = ROWNUM;

Note that conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM emp WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row
to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition
false. All rows subsequently fail to satisfy the condition, so no rows are returned.