Re: Limiting the number of returned rows with Oracle

From: Domenico Vasile <vasile_at_NOSPAMpitagora.it>
Date: 2000/06/06
Message-ID: <MPG.13a6e4f2f0a741f1989680@news.teleline.es>#1/1

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 Received on Tue Jun 06 2000 - 00:00:00 CDT