From: Thomas J. Kyte <tkyte@us.oracle.com>
Subject: Re: Limiting the number of returned rows with Oracle
Date: 2000/06/06
Message-ID: <8hjii8$f5d$1@nnrp1.deja.com>#1/1
References: <393C7E63.345F9DE8@tanyelcp650.brightware.com> <9W2%4.10$574.1171@newreader.ukcore.bt.net> <MPG.13a6e4f2f0a741f1989680@news.teleline.es>
X-Http-Proxy: 1.0 x73.deja.com:80 (Squid/1.1.22) for client 205.227.43.15
Organization: Deja.com - Before you buy.
X-Article-Creation-Date: Tue Jun 06 19:14:33 2000 GMT
X-MyDeja-Info: XMYDJUIDtkyte
Newsgroups: comp.databases.oracle.server
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)


In article <MPG.13a6e4f2f0a741f1989680@news.teleline.es>,
  Domenico Vasile <vasile@NOSPAMpitagora.it> wrote:
> In article <9W2%4.10$574.1171@newreader.ukcore.bt.net>,
> Kevin_A_Lewis@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@Hotmail.com>
> >
> > The views expressed herein by the author of this document
> > are not necessarily those of BOCM PAULS Ltd.
> > <converse@tanyelcp650.brightware.com> wrote in message
> > news:393C7E63.345F9DE8@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@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.