Possible Duplicate:
Rownum not working with query
I am running some tests for how rownum
works. Please could someone explain the output of the following queries - Specifically why the third query is returning 0 rows.
Query 1 - Returns 2456 rows
Select rownum, s.id, s.title, sv.version_id
from software s JOIN software_version sv on (s.id = sv.id);
Query 2 - Returns 100 rows
Select rownum, s.id, s.title, sv.version_id
from software s JOIN software_version sv on (s.id = sv.id)
where rownum between 1 and 100;
Query 3 - Returns 0 rows
Select rownum, s.id, s.title, sv.version_id
from software s JOIN software_version sv on (s.id = sv.id)
where rownum between 101 and 400;
Thanks
rownum is only evaluated AFTER the row is fetched and other predicates are evaluated. that is the key. so if you say
select * from table where rownum >= 2;
it can never work, as it works like
open cursor loop
fetch row (rownum is evaluated as the last thing here)
if fetched then rownum = rownum + 1
end cursor
if you request rownum starting from > 1 then its never true. to do pagination if you need to you have to code like
select *
from (select rownum r, t.*
from your query tables
order by ..)
where r>=101
and rownum <= 400
also read more here at ask tom