oracle paging problem solution

After finishing the project yesterday, I asked the test to test 1, and the test said that the paging query seemed to be ineffective. After turning to page 4, I saw the same data results.
At the time, I was like, no way, the paging component is supposed to be good, why is there a problem? With my doubts, I opened my ide, ran a run on my machine, and sure enough there was a problem.
If you have a problem, look for it:
First, print two sql queries with the same result into the database and execute them:
sql1:

 
select * 
from (select t.*, rownum rn 
from (select t_e_id, t_e_name, t_e_tel, t_e_areacode 
from (select t.eid t_e_id, 
t.ename t_e_name, 
t.etel t_e_tel, 
t.areaid t_e_areacode, 
t.biz_delete_time, 
decode(areaid, '0730', '0', '1') orderseq 
from vr_enterprise t 
where t.eid not in (select eid from t_biz_erelation)) 
order by orderseq, biz_delete_time nulls last) t 
where rownum < 25) 
where rn >= 19 
sql2 :  
select * 
from (select t.*, rownum rn 
from (select t_e_id, t_e_name, t_e_tel, t_e_areacode 
from (select t.eid t_e_id, 
t.ename t_e_name, 
t.etel t_e_tel, 
t.areaid t_e_areacode, 
t.biz_delete_time, 
decode(areaid, '0730', '0', '1') orderseq 
from vr_enterprise t 
where t.eid not in (select eid from t_biz_erelation)) 
order by orderseq, biz_delete_time nulls last) t 
where rownum <18) 
where rn >= 12 

It turns out that most of the rows are identical.
In order to find the problem, it is only necessary to first simplify sql step by step to see where the problem occurs.
So there it is. The problem is where, rownum < At 18, the data changed. Why did the where condition change the result?
I can't figure it out...
baidu, baidu for a long time, no one has given me an explanation...
Later, my colleague said to try another way of writing, so I changed another way of writing, as follows:

 
select * 
from (select t.*, rownum rn 
from (select t_e_id, t_e_name, t_e_tel, t_e_areacode 
from (select t.eid t_e_id, 
t.ename t_e_name, 
t.etel t_e_tel, 
t.areaid t_e_areacode, 
t.biz_delete_time, 
decode(areaid, '0730', '0', '1') orderseq 
from vr_enterprise t 
where t.eid not in (select eid from t_biz_erelation)) 
order by orderseq, biz_delete_time nulls last) t)m 
where m.rn >= 1 and m.rn <25 

This method is effective, effective is effective, the key is why the previous method does not work? Get to the bottom of a problem.
It seems that baidu is no good, we have to change to google. google search always likes to be blocked by china government, no choice but to find an chrome plugin to solve the problem.
To find ah find ah find in oracle ask tom, tom detailed introduces the usage of rownum http here: / / www oracle. com/technetwork/issue - archive / 2006/06 - sep/o56asktom - 086197. html
One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so.
See this sentence, the original use order by order should be added when by only 1, otherwise rownum there will be a problem. Wow ha ha ha, too happy, hard work does not cost people ah, find the crux of the problem, of course, it will be good to solve.
It seems that google is much more powerful than baidu. It is better to learn English carefully in the future, otherwise the problems encountered have not been solved.