Query the top 10 records in Oracle

How does Oracle Query the TOP10 records in a table?

SELECT * FROM test where rownum <=10----Description: RowNum can only be used for < or <= operations, and nested queries are used if you want to use the > operator.

Here is an introduction to RowNum

================================

Use of RowNum and Row_number (), Over ()

RowNum is a pseudo-column provided by Oracle starting from 8, which is to number the results of SQL, always starting from 1, and the common use is to page out the output.

Like what

SELECT * from Torderdetail a WHERE ROWNUM <= 10

This statement is the output of the first 10 records, where the use of similar to the SQL Sever top, but rownum for the specified number range of the output should be said to be more powerful

SELECT *

From (SELECT a.*, ROWNUM rn from Torderdetail a)

WHERE RN >= and RN <= 20

This statement is the output of the 10th to 20th record, here the reason used RowNum RN, is to turn rownum into an instance, because rownum itself can only use <= comparison, only to Narimi column, so you can do >= comparison.

In practical use, it is often required to take the most recent records, which need to sort the records first, then take RowNum <= a value

The General common

SELECT *

From (SELECT a.* to Torderdetail a ORDER by order_date DESC)

WHERE ROWNUM <= 10

And there was a discussion in csdn about the 10 records that were taken, and some people gave such a statement

SELECT a.* from Torderdetail a

WHERE ROWNUM <= 10

ORDER by Order_date DESC

The reason for such a statement, mainly from the efficiency of the consideration, the preceding statement, is to perform a full table scan and then sort, then fetch 10 records, then a statement will not be full table scan, will only take out 10 records, it is obvious that the post-sentence efficiency will be much higher.

Then why is it controversial, that is, in the order of execution of the dispute, is the first sort and then take 10 records, or the first to take 10 records to reorder it? The results of the two sequences are diametrically opposite, the first sort and then fetch 10, that is, take the latest 10, and first take 10, then sort, then remove the earliest 10 records. For this statement, it is generally considered that the order of execution takes 10 records to reorder. So this statement should be an error. In fact, this is not the case, the execution order of this statement and order by field has a relationship, if you order by the field is PK, then the first sort, then take 10 (faster than the first statement faster), and the sort field is not PK, is the first fetch 10 reorder, at this time the result is not the same as the requirements, So the second way to do this is to make sure that the result is correct if the sort field is the primary key.

Row_number () over () This analysis function is provided starting from 9I, and the general purpose is almost the same as rownum.

The general Notation Row_number () over (order by order_date DESC) is generated in the same order as the RowNum statement, and the same is true for the rownum statement with order BY, so in this case the two usages are the same.

And for the group after the most recent 10 records, it is rownum can not be achieved, at this time only row_number could be achieved, row_number () over (the partition by Group field order by sort field) will be able to achieve the group after the number, For example, to take the last 10 order records for nearly one months each day.

SELECT *

From (SELECT a.*, Row_number () through (PARTITION by TRUNC (order_date) Order by Order_date DESC) Rn from Torderdetail a)

WHERE RN <= 10

RowNum's alternative usage, sometimes we encounter this demand, the output of all the days of the month, many people will worry, the database does not have such a table, how to output one months of all the days? It can be solved with rownum:

SELECT TRUNC (sysdate, ' MM ') + ROWNUM-1 from DUAL

CONNECT by ROWNUM <= to_number (To_char (Last_day (sysdate), ' DD '))

Original from the "Bit network", reproduced please keep the original link: http://soft.chinabyte.com/database/27/11420027.shtml

Query the top 10 records in Oracle