How can I return a specific range of ROWNUM
values?
I'm trying the following:
select * from maps006 where rownum >49 and rownum <101
This returns only rows matching the <
operator.
How can I return a specific range of ROWNUM
values?
I'm trying the following:
select * from maps006 where rownum >49 and rownum <101
This returns only rows matching the <
operator.
I was looking for a solution for this and found this great article explaining the solution Relevant excerpt
Now with a real example (gets rows 148, 149 and 150):
I know this is an old question, however, it is useful to mention the new features in the latest version.
From Oracle 12c onwards, you could use the new Top-n Row limiting feature. No need to write a subquery, no dependency on ROWNUM.
For example, the below query would return the employees between 4th highest till 7th highest salaries in ascending order:
You can also do using CTE with clause.
Note the double nested view.
ROWNUM
is evaluated beforeORDER BY
, so it is required for correct numbering.If you omit
ORDER BY
clause, you won't get consistent order.