Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select the top 5 out of a table.
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