Re: Limiting the resultset returned from a SELECT to the first N rows?

Peter Lowagie wrote:
>
> Michael,
>
> Just add an extra condition to your select statement:
>
> SELECT * FROM tbl
> WHERE rownum < X
> ORDER BY amount TOP 10
>
> where x equals number of rows +1 you want to recieve
>
> Michael G. Schneider wrote:
> >
> > I would like to write a SELECT statement and limit the number of returned
> > rows. Something like
> >
> > SELECT * FROM tbl ORDER BY amount TOP 10
> >
> > Is this possible?
> >
> > Michael G. Schneider
> >
> > mgs_software_at_compuserve.com

Stop doing this!!
We have had this misinformation disseminated (and corrected) too many times on this NG.

ROWNUM is evaluated before ORDER BY, so the results of the above version of the query are arbitrary.

In any sensible interface (i.e. NOT SQL*Plus), the correct way is to open a select cursor (including ORDER BY), and explicitly FETCH the number of rows you want.

SQL*Plus allows you no control over the number of rows fetched from an ordered query and should be avoided for most production systems, except as a mechanism for executing very simple statements or, in this case, a PL/SQL block where the appropriate logic can be embedded.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"