Query with "limit" not returning results

Question

This is my query:

select * 
  from (SELECT "MYTABLE".* FROM "MYTABLE" ORDER BY "COMPANY" ASC, "SURNAME" ASC) 
 where ROWNUM between 0 and 20

Which works like expected, but when I try to fetch the next 20 Records with:

select * 
  from (SELECT "MYTABLE".* FROM "MYTABLE" ORDER BY "COMPANY" ASC, "SURNAME" ASC) 
 where ROWNUM between 20 and 40

The result-set is empty? Why is this and what can I do to get the next 20 records?

Are you sure there are results after 20? If you get your results between 0 and 40 do you get 40 results? — koopajah, Mar 05 '13 at 12:29
Because you are not using ROWNUM properly: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html — Plouf, Mar 05 '13 at 12:29

score 4 · Accepted Answer · answered Mar 05 '13 at 12:31

you'd have to nest rownum , as rownum is only incremented once the row is fetched. so saying where rownum > 1 will never return a row. eg

select *
  from (select a.*, rownum r 
          from (select mytable.*
                  from mytable 
                 order by company asc, surname asc) a
         where rownum <= 40
       )
 where r >= 20
 order by r;

or you can use the row_number() analytic

select *
  from (select mytable.*, row_number() over (order by company asc, surname asc) rn
          from mytable) 
 where rn between 20 and 40
 order by rn;

Query with "limit" not returning results

1 Answers1