Relationship between rownum and order by queries in Oracle queries

During application development, it is common to sort data according to certain rules and then retrieve the first few data records. In Oracle, order by is commonly used to obtain data smaller than rownum. However, if you are not familiar with Oracle, you may find that the value retrieved from the SQL statement you wrote is incorrect. Why.

Oracle first Retrieves all the data and sorts the data in the sorting segment. Assume that you have an SQL statement as follows.

Select
Emp_no
From
EMP
Where
Rownum <   10
Order   By
Emp_no

During retrieval, the data is first retrieved, that is, the EMP table data is retrieved, and then the order by operation is performed. Because rownum data is retrieved before sorting, rownum cannot be used to obtain the first 10 operations after sorting. So how should we operate it. In fact, it is very easy to use subqueries, first sort and then get rownum. As shown below

Select
Emp_no
From
(
Select  
Emp_no
From
EMP
Order   By
Emp_no
)
Where
Rownum <   10

In this way, you will find that you can finally retrieve the results you need.