[sql] SQL ROWNUM how to return rows between a specific range

How can I return a specific range of ROWNUM values?

I'm trying the following:

select * from maps006 where rownum >49 and rownum <101

This returns only rows matching the < operator.

SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Id) AS RowNum, * FROM maps006) AS DerivedTable
WHERE RowNum BETWEEN 49 AND 101

I know this is an old question, however, it is useful to mention the new features in the latest version.

From Oracle 12c onwards, you could use the new Top-n Row limiting feature. No need to write a subquery, no dependency on ROWNUM.

For example, the below query would return the employees between 4th highest till 7th highest salaries in ascending order:

SQL> SELECT empno, sal
  2  FROM   emp
  3  ORDER BY sal
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

     EMPNO        SAL
---------- ----------
      7654       1250
      7934       1300
      7844       1500
      7499       1600

SQL>

I was looking for a solution for this and found this great article explaining the solution Relevant excerpt

My all-time-favorite use of ROWNUM is pagination. In this case, I use ROWNUM to get rows N through M of a result set. The general form is as follows:

select * enter code here
  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;

Now with a real example (gets rows 148, 149 and 150):

select *
    from
  (select a.*, rownum rnum
     from
  (select id, data
     from t
   order by id, rowid) a
   where rownum <= 150
  )
   where rnum >= 148;

You can also do using CTE with clause.

WITH maps AS (Select ROW_NUMBER() OVER (ORDER BY Id) AS rownum,* 
from maps006 )

SELECT rownum, * FROM maps  WHERE rownum >49 and rownum <101  

select * 
from emp 
where rownum <= &upperlimit 
minus 
select * 
from emp 
where rownum <= &lower limit ;

SELECT  *
FROM    (
        SELECT  q.*, rownum rn
        FROM    (
                SELECT  *
                FROM    maps006
                ORDER BY
                        id
                ) q
        )
WHERE   rn BETWEEN 50 AND 100

Note the double nested view. ROWNUM is evaluated before ORDER BY, so it is required for correct numbering.

If you omit ORDER BY clause, you won't get consistent order.