[Oracle] ROWNUM and paging

[Oracle] ROWNUM and paging

Rownum is a poor Oracle database, and its order increases progressively according to the order in which records are obtained from the table. Note that because records are stored unordered In the table, therefore, you cannot obtain a result similar to top n through the combination of rownum and order.

Oracle uses rownum to implement Paging

(Oracle) Rownum explain

Rownum for Oracle Database Learning

Summary of Rownum usage in Oracle, differences between Rownum and ROWID

Oracle uses Rownum and rowid for paging

Rownum paging in Oracle Database
 
The test data is as follows:

Select * from test;

ID NAME
------------------------------
1
3 C
4 C
8 C
10 D
2 B
5 C
7 C
6 C
9 D

You can use rownum <= 5 to obtain the first five rows of data:

Select * from test where rownum <= 5;

ID NAME
------------------------------
1
3 C
4 C
8 C
10 D

If you want to obtain results like top n, you must use the subquery:

Select * from (select * from test order by id) where rownum <= 5;

ID NAME
------------------------------
1
2 B
3 C
4 C
5 C

If you want to obtain data between 5th rows and 10th rows, you must add a subquery:

Select T. * from (select t. *, rownum rn from (select * from test order by id) t where rownum <= 10) T where T. rn> 5;

ID NAME RN
----------------------------------------
6 C 6
7 C 7
8 C 8
9 D 9
10 D 10

In fact, the above Code is written by traps. If you don't believe it, replace order by id with order by name:

Select * from (select * from test order by name) where rownum <= 5;

ID NAME
------------------------------
1
2 B
3 C
4 C
8 C
Select T. * from (select t. *, rownum rn from (select * from test order by name) t where rownum <= 10) T where T. rn> 5;

ID NAME RN
----------------------------------------
5 C 6
4 C 7
8 C 8
10 D 9
9 D 10

You will be surprised to find that id = 4 appears in two places, which is not logical! But the truth is, why? Because the name is not unique, the results of the two sorting results may be different. Let me give an example:

Select id, name, rank () over (order by name) from test;

Id name rank () OVER (ORDERBYNAME)
-----------------------------------------------------
1 A 1
2 B 2
6 C 3
3 C 3
4 C 3
8 C 3
5 C 3
7 C 3
9 D 9
10 D 9

From the above results, we can easily find that, by name, multiple pieces of data are listed in 3rd bits. In this way, when the first five names are selected, the number of digits in the parallel position 3rd is not a definite issue, so there is a strange problem. So how can we completely solve this problem? In fact, you only need to add rowid after order by name to ensure that no parallel match will occur, as shown below:

Select * from (select * from test order by name, rowid) where rownum <= 5;

ID NAME
------------------------------
1
2 B
3 C
4 C
8 C
Select T. * from (select t. *, rownum rn from (select * from test order by name, rowid) t where rownum <= 10) T where T. rn> 5;

ID NAME RN
----------------------------------------
5 C 6
7 C 7
6 C 8
10 D 9
9 D 10