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

This returns only rows matching the < operator.

See [**How ROWNUM works in a pagination query?**](http://stackoverflow.com/questions/30321483/how-rownum-works-in-pagination-query/30321788#30321788) — Lalit Kumar B, Apr 20 '16 at 07:42

score 74 · Accepted Answer · answered Dec 29 '10 at 09:07

74

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

answered Dec 29 '10 at 09:07

Michael Pakhantsov

24,855
6
60
59

Just for the sake of completeness, is there a way to remove the r column after? – Dennis Jul 02 '12 at 13:42
3

My quick fix is to change the first line to: `SELECT col1,col2,col3 from` where col1, col2 etc would be the names of all columns except r – Dennis Jul 02 '12 at 13:46
2

this doesn't work on oracle if you don't give ROWNUM an alias – Julio Marins Nov 18 '14 at 13:23
2

How to select all rows without rownum in result's set? – Vladimir Nov 04 '15 at 12:28
1

I get inconsistent results in Oracle using this pattern. The double nested query below fixes the problem. – geneorama Dec 08 '15 at 18:08
Oracle evaluates ROWNUM for the ">" and "<" comparisons like a Union, it seems. So I would not recommend using the Where clause this way with rownum. – The Original Android Oct 20 '17 at 17:17
@Vladimir, Remove * in the outer select and make the column select as you required. (Using the query in the accepted) – ram Feb 21 '19 at 11:01

score 33 · Answer 2 · answered Dec 29 '10 at 09:10

33

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.

answered Dec 29 '10 at 09:10

Quassnoi

413,100
91
616
614

I get "ORA-00904: "RN": invalid identifier" when I try something similar. – Robin Green Feb 26 '14 at 11:11
@RobinGreen: please make another question and post the "something similar" there. – Quassnoi Feb 26 '14 at 11:18
I am still not getting why double nesting is required. I am trying with some data and it is giving me consistent records.`select * from (SELECT tab.*, rownum r FROM table1 tab order by cft.confrm_fraud_id) tab1 WHERE tab1.r BETWEEN 10 AND 20` – Ubercool Apr 02 '15 at 11:24
1

@pramod: that's because your query reads records in order of `confrm_fraud_id`, most probably using an index on it. You can't rely on this behavior. Compare these two queries: http://sqlfiddle.com/#!4/4730c5/5 (incorrect) and http://sqlfiddle.com/#!4/4730c5/7 (correct). Note that in the incorrect query the `ROWNUM` even come in the wrong order. – Quassnoi Apr 02 '15 at 14:14
This solution worked perfectly for me. I was trying to use `ROWNUM BETWEEN 11 AND 20` for pagination on a result set with 12 rows, but was getting `0` records for any range that started `>1`. Thanks for the tip! – cyberbit May 26 '16 at 17:14
Hi All , why don't we use joins instead of suqueries ? It should be more efficient... – Kandy Dec 08 '16 at 12:45
@Kandy: we use both joins and subqueries, this is even more efficient. – Quassnoi Dec 08 '16 at 13:50

Lalit Kumar B · Answer 3 · 2015-05-19T09:56:04.283

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>

score 6 · Answer 4 · answered Dec 20 '13 at 19:15

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;

score 2 · Answer 5 · edited Sep 19 '13 at 08:31

2

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

edited Sep 19 '13 at 08:31

Noel

10,152
30
45
67

answered Sep 19 '13 at 08:23

subrahmanyam j

21
1

score 2 · Answer 6 · answered Dec 29 '10 at 09:09

2

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

answered Dec 29 '10 at 09:09

Ghyath Serhal

7,466
6
44
60

score 2 · Answer 7 · answered Dec 29 '10 at 09:12

2

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

answered Dec 29 '10 at 09:12

Pankaj Agarwal

11,191
12
43
59

SQL ROWNUM how to return rows between a specific range

7 Answers7

Linked

Related