Paginating Results with the Oracle DB

While the Oracle DB has some very powerful functionality, it also seems to be missing some very basic functionality. Many developers, including myself, have often struggled with paginating results from an Oracle query. Even after Googling, I find it difficult to find a straightforward solution. So here it is, taken from the implementation in the pretty amazing Oracle Enhanced ActiveRecord Adapter for Ruby on Rails.

The basic idea is to wrap your query with two outer SELECTs. Each one will limit the result's rownum property:

select 
  * 
from (
  select 
    raw_sql_.*, rownum raw_rnum_ 
  from 
    (<YOUR SQL HERE>) raw_sql_ 
  where 
    rownum <= <UPPER LIMIT>
) 
where 
  raw_rnum_ > <LOWER LIMIT>

Where <UPPER LIMIT> is the upper row number you want, and <LOWER LIMIT> is the initial row offset. So say you want to return results 11 - 20 from this query:

SELECT * FROM news_stories ORDER BY date_created DESC

Your query would look like this:

select 
  * 
from (
  select 
    raw_sql_.*, rownum raw_rnum_ 
  from 
    (SELECT * FROM news_stories ORDER BY date_created DESC) raw_sql_ 
  where 
    rownum <= 20
) 
where 
  raw_rnum_ > 10