Re: First 20 records from a select, after ordering

This will work:

select rownum, <column>
from <table>
where rownum < 41
having rownum > 19
group by rownum, <column>

Chad Sheley <csheley_at_usa.capgemini.com> wrote in message news:_FDU3.147$c96.12911_at_news.uswest.net...
> 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?
>
> 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
> >---------------------------------------------------------------
> >
> >
>
>
Received on Fri Nov 05 1999 - 11:06:13 CST