Ask Tom: On ROWNUM and Limiting Results

Ask Tom: On ROWNUM and Limiting Results: "I use it for two main things:

* To perform top-N processing. This is similar to using the LIMIT clause, available in some other databases.

select *
 from
(select *
  from t
 order by unindexed_column)
where ROWNUM < :N;

* To paginate through a query, typically in a stateless environment such as the Web.

select *
 from ( select /*+ FIRST_ROWS(n) */
 a.*, ROWNUM rnum
     from ( your_query_goes_here,
     with order by ) a
     where ROWNUM <=
     :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

"