Pagination SQL query in Oracle

Let say you have 10,000 records in your table (say NEWS). Your web front end only wants to display the first 10 news (sorted by publish_time).

SELECT * FROM NEWS
WHERE ROWNUM <= 10 and ROWNUM > 0
order by publish_time

However, this doesn’t work.

The correct pagination query will look like this:

SELECT *
FROM (SELECT fullset.*, ROWNUM low_value
FROM (SELECT * FROM NEWS order by publish_time)  fullset
WHERE ROWNUM <= 10) WHERE low_value > 0

 

(Visited 5 times, 1 visits today)