The effect of Oracle rownum on sorting

1. The standard rownum paging query usage method:

SELECT *
From (select C.*, rownum rn from content C)
where RN >= 1
and RN <= 5

2. However, if you add order by Addtime, the data is not displayed correctly

SELECT *
From (select C.*, rownum rn from content C ORDER by Addtime)
where RN >= 1
and RN <= 5

Solution, and then add a layer of query, you can solve,

SELECT *
From (select RowNum RN, t.*
From (select Title, Addtime from content order by addtime desc) t)
where RN >= 1
and RN <= 5

If you want to take into account the efficiency of the problem, the above can also be optimized to (the main difference)

SELECT *
From (select RowNum RN, t.*
From (select Title, Addtime from content order by addtime Desc) t
where RowNum <= 10)
where RN >= 3

The effect of Oracle rownum on sorting