Oracle paging sorting

OracleFour Common query statements for paging and sorting

IT maopu technology Source: itmop.com Author: Edit finishing CLICK: 318 times

In oracle, how does one implement the paging and sorting functions of query statements? The implementation method may be different from that of ms SQL Server. This is mainly because of the similarities and differences in the processing of result row numbers in the two databases. In ms SQL Server, you can use the combination of the 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 describe it here, how to Use ROWNUM to implement some common paging and sorting queries? For the concept of ROWNUM, you can search for it at will. There are a lot of detailed explanations, so I will not go into details.

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. 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 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.