Oracle Top 10 Records

Source: Internet
Author: User

How does Oracle Query the TOP10 records in a table?


From Test

where RowNum <=10

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


From Torderdetail A


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



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, the most recent records are often requested, which requires sorting the records before taking RowNum <=

The General common


From (SELECT a.*

From Torderdetail A

ORDER by Order_date DESC)


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


From Torderdetail A


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.

Why is it controversial, that is, in the order of execution of the dispute, is the first to perform a sort of 10 records, or take 10 records, and then sort? 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.


From (SELECT a.*,

Row_number () over (PARTITION by TRUNC (order_date) Order by Order_date DESC)


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


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


Oracle Top 10 Records

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.