SQL ROWNUM how to return rows between a specific range

Question

How can I return a specific range of ROWNUM values   I m trying the following   select   from maps006 where rownum  gt 49 and rownum  lt 101   This returns only rows matching the  lt  operator

User · Answer

select    from emp  where rownum  lt    amp upperlimit  minus  select    from emp  where rownum  lt    amp lower limit

User · Answer

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  gt 49 and rownum  lt 101

User · Answer

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  lt           MAX ROW TO FETCH    where rnum   gt    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  lt   150        where rnum  gt   148

User · Answer

SELECT   from     select m    rownum r  from maps006 m     where r  gt  49 and r  lt  101

User · Answer

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

User · Answer

SELECT   FROM  SELECT ROW NUMBER   OVER ORDER BY Id  AS RowNum    FROM maps006  AS DerivedTable WHERE RowNum BETWEEN 49 AND 101

User · Answer

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 gt  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 gt

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

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to rownum