Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Select the top 5 out of a table.

Re: Select the top 5 out of a table.

From: Brian Beuning <bbeuning_at_mindspring.com>
Date: 2000/07/15
Message-ID: <39713106.6E412218@mindspring.com>#1/1

I have been told rownum is dangerous. It this example it will pull the entire table into the server. It will also place a share lock on all the rows, even though you are only looks at 5 of the rows.

Brian Beuning

tigsar_at_my-deja.com wrote:

> select * from
> (select name, salary from employees order by salary desc)
> where rownum<5
>
> In article <B6Jb5.5505$SU5.133624_at_afrodite.telenet-ops.be>,
> "Haplo" <valgaeren_at_pandora.be> wrote:
> > For example:
> >
> > table employees with two columns name, salary
> >
> > When asked for the 5 people with the highest salary, I would do a select
> > like this :
> >
> > SELECT name, salary
> > FROM employees emp1
> > WHERE 5 > (SELECT COUNT(salary )
> > FROM employees emp2
> > WHERE emp2.salary > emp1.salary)
> > ORDER BY salary;
> >
> > But I've heard that there is a better (more performant) solution than this.
> > Does anybody know how?
> >
> > Thanks,
> >
> > Wim Valgaeren
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Jul 15 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US