Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Limiting the number of returned rows with Oracle

Re: Limiting the number of returned rows with Oracle

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/06
Message-ID: <8hjii8$f5d$1@nnrp1.deja.com>#1/1

In article <MPG.13a6e4f2f0a741f1989680_at_news.teleline.es>,   Domenico Vasile <vasile_at_NOSPAMpitagora.it> wrote:
> In article <9W2%4.10$574.1171_at_newreader.ukcore.bt.net>,
> Kevin_A_Lewis_at_Hotmail.com says...
> > adding the clause 'where rownum < 5'
> >
> > will have the effect of only returning 4 rows.
> >
> > However in the relational world there is no guarantees as to which
 rows
> > these will be. You may need to 'order' them if you need to pick out
 either
> > end of a sequence.
> >
> > Regards
> >
> > --
> > Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich
 United
> > Kingdom)
> > <Kevin_A_Lewis_at_Hotmail.com>
> >
> > The views expressed herein by the author of this document
> > are not necessarily those of BOCM PAULS Ltd.
> > <converse_at_tanyelcp650.brightware.com> wrote in message
> > news:393C7E63.345F9DE8_at_tanyelcp650.brightware.com...
> > > Is there a way to limit the number of returned records to a given
 number
> > > with Oracle with SQL?
> > >
> > > Something like: "select * from table and return first five rows"
> > >
> > > thanks,
> > >
> > > Cem
> > >
> > >
> > >
> >
> >
> >
> In at least two Oracle's official manuals is reported a method to
 perform
> the so called 'Top N query'. It's something like this:

>

> SELECT *
> FROM (SELECT <Selection list>
> FROM <Table>
> ORDER BY <Some Field>)
> WHERE rownum < N;

>
> It seems ok but doesn't work properly. Oracle issue a strange
 syntactical
> error. Any comments?
> Thanks in advance.
>

> -Domenico Vasile

>

order by in a subquery was added with 8.1 of the server. in 8.0 and before, that did not work (syntax error)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Jun 06 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US