101. ROWNUM - llighter/database GitHub Wiki

ROWNUM Pseudocolumn Oracle Help Center

오라클 rownum 사용 팁 rownum이란? 2015.03.29 블로그 kimtu89

The ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query.
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;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.
For example, the following query returns the employees with the 10 smallest employee numbers.
This is sometimes referred to as top-N reporting :

   SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.