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:
FROM (SELECT fullset.*, ROWNUM low_value
FROM (SELECT * FROM NEWS order by publish_time) fullset
WHERE ROWNUM <= 10) WHERE low_value > 0