Skip to main content
Database Administrators

Return to Revisions

1 of 2
user avatar
user avatar

Using rownum without an order by will give you random results each time you run your query. To do a proper paging, you need to put this into a derived table together with an order by

select *
from (
  select foo.*
  from foo
  order by some_column -- this is important!!
) t
where rownum between 101 and 200;

For more details please read the explanation on rownum works in the manual http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns009.htm#SQLRF00255

For a very good discussion on how to do paging efficiently in Oracle (and other DBMS) I can highly recommend this site: http://use-the-index-luke.com/sql/partial-results/fetch-next-page

user1822