Oracle queries the first 10 records and paging queries (5th to 10th records), and oracle queries the first 10 records

Oracle queries the first 10 records and paging queries (5th to 10th records), and oracle queries the first 10 records

If you want to know the actual application solutions of the first 10 records in Oracle query, you can click the following article to make the correct usage in the actual operations, I have a better understanding. I hope that the following articles will be detailed in the text.

In Oracle, how does one query the top 10 records in a table?

select *  from test  where rownum <=10  

The following is an introduction to rownum.

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 a  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 a)  WHERE rn >= 10 AND rn <= 20  

This statement outputs 10th to 20th records from Oracle queries. rownum rn is used here 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 a.*  FROM torderdetail a  ORDER BY order_date DESC)  WHERE ROWNUM <= 10  

I have discussed CSDN. Someone has given such a statement about taking nearly 10 records.

SELECT a.*  FROM torderdetail a  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 the opposite. When Oracle queries the first 10 records, it first sorts and then obtains 10 records, that is, the last 10 records, and then obtains 10 records, 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 a.*,  ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC)  rn  FROM torderdetail a)  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'))  

The above content introduces the first 10 records of Oracle query.

 

To sum up, oracle queries 5th to 10th records by page:

Select * from (select a. *, rownum rn from (select * from scott. emp) a where rownum <= 10) where rn> = 5;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.