Four query statements for Oracle paging and sorting

What query statements do you know about completing the Oracle paging and sorting functions in Oracle? The actual operation method may be different from that of ms SQL Server, mainly because of the similarities and differences in the processing of result row numbers in the two related databases.

In ms SQL Server, you can use the combination of top and ROW_NUMBER () methods to achieve this. Generally, the writing method is relatively simple, while oracle is very different. It can only be implemented through ROWNUM, here we will mainly explain how to use ROWNUM to implement some common paging and sorting queries. The concept of ROWNUM can be searched at will, and many detailed explanations are clear, I will not go into details.

1. query the first 10 records

 
 

  
  
SELECT * FROM TestTable WHERE ROWNUM <= 10 
 
 

2. The second of four common query statements used for paging and sorting in Oracle, and the second is to query 11th to 20th records.

 
 

  
  
SELECT * FROM (SELECT TestTable.*, ROWNUM ro FROM TestTable WHERE ROWNUM <=20) WHERE ro > 10 
 
 

3. The first 10 records sorted by name field in ascending order

 
 

  
  
SELECT * FROM (SELECT * FROM TestTable ORDERY BY name ASC) WHERE ROWNUM <= 10 
 
 

4. 11th to 20th records sorted by name field in ascending order

 
 

  
  
SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM TestTable ORDER BY name ASC) 
tt WHERE ROWNUM <=20) WHERE ro > 10 
 
 

The preceding statement may be the most commonly used statement in query sorting and Oracle paging. No conditions are added because the statement structure is not affected. Although the performance may be affected, it may only be possible to query more data in the innermost layer of the query, but it should have little impact on the re-sorting and re-selection of the outer layer.