How to query top 10 records in an oracle table

The following is an introduction to rownum.
======================================
3. Use of rownum and row_number () over ()
Rownum is a pseudo column provided by Oracle starting from 8. It numbers the SQL results and starts from 1. It is commonly used for paging output.
For example
Select *
From torderdetail
Where rownum <= 10
This statement is used to output the first 10 records, which is similar to SQL Server top. However, rownum is more powerful for the output of the specified number range.
Select *
From (select a. *, rownum Rn
From torderdetail)
Where rn> = 10 and Rn <= 20
This statement outputs 10th to 20th records. In this example, rownum RN is used to convert rownum into an instance, because rownum itself can only be compared with <=, but only into a real column, in this way, we can compare the values of> =.
In actual use, it is often required to take the latest records, which requires sorting records and then getting rownum <=
Common
Select *
From (select .*
From torderdetail
Order by order_date DESC)
Where rownum <= 10
I have discussed csdn. Someone has given such a statement about taking nearly 10 records.
Select .*
From torderdetail
Where rownum <= 10
Order by order_date DESC
The reason for such a statement is mainly in terms of efficiency. The first noodle statement requires a full table scan and then sorting, and then taking 10 more records, the next statement will not scan the entire table, but will retrieve 10 records. Obviously, the efficiency of the next statement will be much higher.
So why is there a controversy? Is there a dispute over the execution order, that is, whether to first execute the sorting to get 10 records, or to get 10 records and then sort them? The results obtained from the two orders are in the opposite order. Sort the results first and then obtain 10, that is, take the last 10, and first take 10, and then sort the results, the first 10 records are retrieved. For this statement, it is generally believed that the execution order is to take 10 records before sorting. Therefore, this statement should be incorrect. But this is not the case. The execution sequence of this statement is related to the order by field. If your order by field is PK, It is sorted first, take 10 more (faster than the first statement), and if the sorting field is not a PK, take 10 first and then sort. The result is different from the requirement, therefore, the second method must ensure that the result is correct only when the sorting field is the primary key.
The row_number () over () analysis function is provided starting from 9i. The general purpose is similar to rownum.
Generally, row_number () over (order by order_date DESC) is generated in the same order as rownum statements, with the same efficiency (for rownum statements with the same order by statement ), in this case, the two methods are the same.
Rownum cannot be used to obtain the latest 10 records after grouping. In this case, only row_number can be used. row_number () over (partition by group field order by sorting field) the Group ID can be implemented. For example, you need to obtain the last 10 order records every day in the last month.
Select *
From (select .*,
Row_number () over (partition by trunc (order_date) order by order_date DESC)
Rn
From torderdetail)
Where rn <= 10
The alternative usage of rownum is that we sometimes encounter this requirement to output all the days of the current month. Many people will be troubled, and there is no such table in the database. How can we output all the days of a month? Use rownum to solve the problem:
Select trunc (sysdate, 'mm') + rownum-1
From dual
Connect by rownum <= to_number (to_char (last_day (sysdate), 'dd '))