Oracle paging query of duplicate data

The Oracle paging query is generally implemented as follows:
Select * from (
Select a. *, rownum r from (
Your own SQL
) A where rownum <=?
) Where r>?

If this SQL statement is sorted by a field containing repeated values

Duplicate records will be found on different pages, and some records cannot be found (not necessarily)

The test code is as follows:

Create table test (
ID VARCHAR2 (10) not null,
OD VARCHAR2 (10) NULL
)
GO

-- Insert Test Data
Insert into TEST
Select level, case when level <= 3 then level else null end from dual connect by level <= 10
Go

-- Test paging results
Article 1-5
Select * from (
Select a. *, rownum r from (
Select t1.id from TEST t1
Order by t1.od desc
) A where rownum <= 5
) Where r> 0
Returned results
Row ID R
1 4 1
2 8 2
3 7 3
4 6 4
5 5 5

Article 6-10
Select * from (
Select a. *, rownum r from (
Select t1.id from TEST t1
Order by t1.od desc
) A where rownum <= 10
) Where r> 5
Returned results
Row ID R
1 6 6
2 5 7
3 3 8
4 2 9
5 1 10

We can see that id = 5 and id = 6 in the first result set have not found the records with id = 9 and id = 10 at the same time.

The solution is as follows:
If the preceding two conditions are met
Add the primary key of the table after order;
In this example
Change to order by t1.od desc, And the phenomenon disappears after t1.id

If there is a better solution, I hope to inform =

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12