SQL ROWNUM how to return rows between a specific range

 SELECT * from ( select m.*, rownum r from maps006 m ) where r > 49 and r < 101

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.

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       1600SQL>