[Oracle] RowNum and Paging

RowNum is a fake of Oracle, and its order increments according to the order in which records are fetched from the table, it is important to note that because the records are stored in the table in a disorderly way, you cannot get a result similar to top n by a simple combination of rownum and order by.

Our test data is as follows:

SELECT * from test;        ID NAME------------------------------         1 A         3 C         4 C         8 C        ten D         2 B         5 c         7 C         6 C         9 D

The first 5 rows of data can be obtained through rownum<=5:

SELECT * FROM Test where rownum<=5;        ID NAME------------------------------         1 A         3 C         4 C         8 C        Ten D

If you want to get results like top N, you must use a subquery:

SELECT * FROM (SELECT * from test order by ID) where rownum<=5;        ID NAME------------------------------         1 A         2 B         3 C         4 C         5 C

If you want to get data between rows 5th through 10th, you must add another layer of subqueries:

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        D                            10

In fact, the above is written by the trap, do not believe you to change the order by ID to order by name try:

SELECT * FROM (SELECT * from Test order by name) where rownum<=5;        ID NAME------------------------------         1 A         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        D                             9         9 D                            10

You will be surprised to find id=4 this data appears in two places, this illogical! But the truth is, why? Because name is not unique, the result of two-order extraction may be different, I would like to 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        Ten D                                          9

From the above results we are not difficult to find, according to the name sort, there are multiple rows of data in the 3rd place, so that when the top 5, in the end of the 3rd in the side of the number is not a definite thing, so there is a previous strange problem. So, how can we solve this problem completely? In fact, as long as the order by name after the addition of ROWID, to ensure that there is no side-by-side situation, as follows:

SELECT * FROM (SELECT * from Test order by Name,rowid) where rownum<=5;        ID NAME------------------------------         1 A         2 B         3 C         4 C         8 Cselect 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        D                             9         9 D                            10