Table rownum returns 0 records if rownum is tested for value greater than 100

Question

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

Solution

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

Comments

[Gordon Linoff]

I think the Oracle docmentation does a perfectly good job explaining why this is the case: docs.oracle.com/cd/B19306_01/server.102/b14200/….