Re: FETCH a Specific Amount of Rows from Query

> Hi,
>
> I know in DB2 you can use the FETCH keyword to get a specific amount of rows
> from a query. Example, SELECT * FROM A FETCH FIRST 10 ROWS ONLY, will return
> on the first 10 rows of a result set. How is this done in Oracle 8i., or is
> it even possible? The FETCH keyword is used differently in Oracle 8i as I
> understand it.

You can use rownum like so:

  select * from a where rownum < 11;

but don't use it like this:

  select * from a where rownum < 11 order by x;

This would fetch ten rows and order it afterwards, which most probably is not what you want. If you want to retrieve the first ten rows of an ordered resultset, go for something like this:

  select * from (
    select a.*, row_number() over (order by x) r     from a
  ) where r < 11;

hth
Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html