How to query the first 10 records in oracle

In Oracle, you can use the where clause with rownum to query the first ten records. The where clause is used to limit the conditions of the query, and rownum is used to limit the total number of rows returned for the query. The syntax is "select * from table Name where rownum

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query the top 10 records in Oracle

How to query the top 10 records in the table in Oracle?

　　select *
　　from test
　　where rownum <=10

The following is an introduction to rownum

The use of Rownum and row_number() over()

ROWNUM is a pseudo column provided by Oracle starting from 8. It is a SQL The results are numbered, always starting from 1. A common use is to output in pages.

For example,

　　SELECT *
　　FROM torderdetail a
　　WHERE ROWNUM <= 10

This statement is to output the first 10 records. The purpose here is similar to The top of sql sever, but rownum should be said to be more powerful for the output of the specified number range

　　SELECT *
　　FROM (SELECT a.*, ROWNUM rn
　　FROM torderdetail a)
　　WHERE rn >= 10 AND rn <= 20

This statement outputs the 10th to 20th records. The reason why rownum rn is used here is to convert rownum into Example, because rownum itself can only use the = comparison can be done.

In practical applications, it is often required to get the most recent records. This requires sorting the records first and then getting rownum

Generally common

　　SELECT *
　　FROM (SELECT a.*
　　FROM torderdetail a
　　ORDER BY order_date DESC)
　　WHERE ROWNUM <= 10

There has been a discussion in CSDN. Regarding the 10 closest records, someone gave such a statement

　　SELECT a.*
　　FROM torderdetail a
　　WHERE ROWNUM <= 10
　　ORDER BY order_date DESC

The reason why such a statement appears is mainly from the perspective of efficiency. The previous statement , it is necessary to scan the entire table and then sort, and then fetch 10 records. The latter statement will not scan the entire table, but will only fetch 10 records. Obviously, the efficiency of the latter statement will be much higher.

Then why is there a dispute? The dispute lies in the order of execution. Should the sorting be performed first to get 10 records, or should the 10 records be taken and then sorted? The results of fetching in the two orders are completely opposite. Sorting first and then fetching 10 records will fetch the 10 most recent records. Fetching 10 records first and then sorting will fetch the earliest 10 records. For this statement, it is generally believed that the execution order is to first fetch 10 records and then sort them. So this statement should be an error. But this is not actually the case. The execution order of this statement is related to the field of order by. If the field of your order by is pk, it is sorted first, and then 10 items are taken (faster than the first statement), and the sorting field When it is not PK, 10 items are taken first and then sorted. At this time, the result is different from the requirements, so the second way of writing must ensure that the result is correct when the sorting field is the primary key.

The analysis function Row_number() over() has been provided since 9I. Its general purpose is similar to rownum.

The general writing method row_number() over(order by order_date desc) generates the same order as the rownum statement, and the efficiency is the same (for the rownum statement that also has order by), so in this case both The usage is the same.

For grouping, taking the latest 10 records cannot be achieved by rownum. At this time, only row_number can be achieved. row_number() over(partition by grouping field order by sorting field) can be achieved after grouping. Number, for example, if you want to get the last 10 order records of each day in the past month

　　SELECT *
　　FROM (SELECT a.*,
　　ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC)
　　rn
　　FROM torderdetail a)
　　WHERE rn <= 10

Alternative usage of Rownum, sometimes we will encounter this demand, requiring the output of all the days of the month, many people will be troubled, There is no such table in the database, so how can we output all the days in a month? It can be solved with rownum:

　　SELECT TRUNC (SYSDATE, 'MM') + ROWNUM - 1
　　FROM DUAL
　　CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), &#39;dd&#39;))

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query the first 10 records in oracle. For more information, please follow other related articles on the PHP Chinese website!