SQL ROWNUM how to return rows between a specific r

2019-01-05 01:45发布

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.

7条回答
趁早两清
2楼-- · 2019-01-05 02:12
 SELECT * from
 (
 select m.*, rownum r
 from maps006 m
 )
 where r > 49 and r < 101
查看更多
老娘就宠你
3楼-- · 2019-01-05 02:21
select * 
from emp 
where rownum <= &upperlimit 
minus 
select * 
from emp 
where rownum <= &lower limit ;
查看更多
Deceive 欺骗
4楼-- · 2019-01-05 02:22

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;
查看更多
时光不老,我们不散
5楼-- · 2019-01-05 02:34

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>
查看更多
SAY GOODBYE
6楼-- · 2019-01-05 02:36

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  
查看更多
够拽才男人
7楼-- · 2019-01-05 02:37
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.

查看更多
登录 后发表回答