Re: Ranging returned rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 04 Feb 2000 10:26:56 -0500
Message-ID: <srrl9s4a2eea9m8d6iqn12jf97eprutroj@4ax.com>

A copy of this was sent to "Milen A. Radev" <milenr_at_yahoo.com> (if that email address didn't require changing) On Thu, 3 Feb 2000 12:00:26 +0200, you wrote:

>How can I limit the number of returned rows?
>For example if I want to show only
>rows from 21-30?
>
>I tried "SELECT * FROM some_table WHERE ROWNUM > 20
> AND ROWNUM < 31", but in the Oracle SQL Reference
>is said that WHERE clause with condition checking whether
>ROWNUM is bigger than any positive number is always
>false, so there is no rows found.
>
>In MySQL (which is simpler and leaner) there is a keyword
>"LIMIT m,n" which do the jobs. But I can't find anything
>similar in Oracle.
>
>
>Milen A. Radev
>
>
>

select * from
( select a.*, rownum rnum from T where .... ) where rnum between A and B
/

scott_at_dev8i> select * from
  2 ( select emp.*, rownum rnum from emp )   3 where rnum between 7 and 10;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
DEPTNO       RNUM

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
10          7
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
20          8
      7839 KING       PRESIDENT            17-NOV-81       5000
10          9
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
30         10

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 04 2000 - 09:26:56 CST