How to Implement the Oracle paging and sorting functions in the database?

Do you know how to correctly implement specific query statements for Oracle paging and sorting in Oracle databases? The implementation method is different from that of ms SQL Server, mainly because of the similarities and differences in the processing of result row numbers in the two Oracle databases.

In ms SQL Server, you can use the combination of top and ROW_NUMBER () methods to achieve this. Generally, the writing method is simple, while Oracle is quite different.

It can only be implemented through ROWNUM. Here we will mainly explain how to use ROWNUM to implement some common Oracle paging and sorting queries. We can search for the ROWNUM concept at will, I will not go into details if many detailed explanations are clear.

1. query the first 10 records

 
 

  
  
SELECT * FROM TestTable WHERE ROWNUM <= 10 
 
 

2. query 11th to 20th records

 
 

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

3. Oracle paging and sorting: 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.