| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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 don't know about the share lock (it's not a select for update- does Oracle lock this?) but I can only see 1 alternative to finding the highest x of something on a table WITHOUT selecting the whole table and using rownum: (I am excluding cases where you can limit something via a where, as in where hiredate > 1/1/90)
define a cursor
create a local array variable of same type as salary
loop through the cursor
see if the current variable is higher than any of the 5 table
entries; if so, record a unique identifier, such as emp_no & update
the array.
then select those 5 rows
seems like a lot more work. Any other way to do this??
On Sat, 15 Jul 2000 23:50:30 -0400, Brian Beuning <bbeuning_at_mindspring.com> wrote:
>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.
>
 |
 |