Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL - SELECT x rows...
I am using the Top-N query, and it seems to work fine...... I'm running Oracle 8.1.6.1 and NT 4.0 sp6
-- "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:8mtr3f$qgd$1_at_soap.pipex.net...Received on Thu Aug 10 2000 - 00:00:00 CDT
> No
>
> if you do an explain plan on this you'll see that Oracle gets 100 rows
THEN
> does the order by. As Mark said you need to do a subquery to do a TOP N.
> "Avi Abrami" <aabrami_at_intersystemsww.com> wrote in message
> news:39924620.BFC59CAC_at_intersystemsww.com...
> > Joseph Ranseth wrote:
> >
> > > How can I select 'x' number of rows from/in a query?
> > >
> > > ie: I want to select the 20 oldest employees, or 100 highest paid,
etc.
> > >
> > > Is there a simple way of going about this, or do I have to select all
of the
> > > records and then only print the desired amount (That wouldn't be
efficient,
> > > though, would it?)
> > >
> > > Thanks in Advance,
> > >
> > > JR
> >
> > Hi Joseph,
> > The only way I know to do this is:
> >
> > SELECT *
> > FROM table
> > WHERE rownum <= 100
> > ORDER BY salary DESC
> >
> > Should give you the 100 highest paid employees.
> > Note the above is from memory only - I haven't
> > actually tried it (just lazy I guess).
> >
> > Cheers,
> > Avi.
> >
> >
>
>