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