Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: First 20 records from a select, after ordering
A copy of this was sent to "Chad Sheley" <csheley_at_usa.capgemini.com>
(if that email address didn't require changing)
On Fri, 5 Nov 1999 10:34:00 -0600, you wrote:
>This is a very timely topic for me as I'm trying to implement a "retrieve
>the X number of rows" sort of thing. I tried this ROWNUM technique, but was
>only able to constrain the first rows from the SELECT. Does anyone know how
>to get the 20th to 40th rows back?
>
select * from
(
select a.*, rownum r
from ( select * from T order by <whatever> ) a
where rownum <= 40
)
where r >= 20
/
for example:
1 select * from
2 (
3 select a.*, rownum r
4 from ( select ename, empno, sal from emp order by sal ) a
5 where rownum <= 4
6 )
7* where r >= 2
scott_at_8i> /
ENAME EMPNO SAL R ---------- ---------- ---------- ----------
JAMES 7900 950 2 ADAMS 7876 1100 3 WARD 7521 1250 4
scott_at_8i> select ename, empno, sal from emp order by sal;
ENAME EMPNO SAL
---------- ---------- ----------
SMITH 7369 800 JAMES 7900 950 ADAMS 7876 1100 WARD 7521 1250 MARTIN 7654 1250 MILLER 7934 1300 TURNER 7844 1500 ALLEN 7499 1600 CLARK 7782 2450 BLAKE 7698 2850 JONES 7566 2975 SCOTT 7788 3000 FORD 7902 3000 KING 7839 5000
14 rows selected.
>Thanks,
>
>Chad Sheley
>csheley_at_usa.capgemini.com
>Senior Consultant
>Cap Gemini
>Des Moines, IA
>
>Stephan Born wrote in message <38200339.CB7157F8_at_beusen.de>...
>>> > >
>>> > > If you don't want to use PL/SQL then you could do something like
>this:
>>> > >
>>> > > SELECT *
>>> > > FROM (SELECT <columns>
>>> > > FROM <tablename>
>>> > > ORDER BY <column>)
>>> > > WHERE rownum < 21
>>> > >
>>> > > This would do your job. HTH
>>> >
>>> > I thought this was only available in 8i, not 8....
>>> >
>>> I don't see why it wouldn't work in earlier versions than that.
>>
>>This is definitely a feature since Oracle 8.xxxx. Ora 7.x cant do this.
>>
>>It seems not to function in Oracle 8.0.5 as I tried it
>>
>>Regards, Stephan
>>--
>>---------------------------------------------------------------
>>Dipl.-Inf. (FH) Stephan Born | beusen Consulting GmbH
>>fon: +49 30 549932-17 | Landsberger Allee 392
>>fax: +49 30 549932-29 | 12681 Berlin
>>mailto:stephan.born_at_beusen.de | Germany
>>---------------------------------------------------------------
>> PGP-Key verfügbar | PGP-Key available
>>---------------------------------------------------------------
>>
>>
>
--
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 Nov 05 1999 - 11:46:23 CST