| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL
On Wed, 21 Jul 1999 09:41:01 -0500, "Glen Siferd" <siferd_at_admin.uwex.edu> wrote:
>Two methods, courtesy of Joe Celko. And a plug for his book, by the way,
>"Joe Celko's SQL for Smarties" (Morgan-Kaufmann, ISBN 1-55860-323-9).
>
>select distinct a.empno, a.sal
> from scott.emp a
>where 3 >= (select count(*)
> from scott.emp b
> where a.sal < b.sal)
> order by a.sal desc;
>
>or
>
>select a.empno, a.sal
> from scott.emp a, scott.emp b
>where a.sal <= b.sal
> group by a.empno, a.sal
>having count(distinct b.sal) <= 3
>order by a.sal desc;
The only problem with either of these queries is that they do not answer the original questions.
The original posting wanted two things
Modifying you answer you get...
1 select distinct a.sal
2 from scott.emp a
3 where 3 >= (select count(*)
4 from scott.emp b 5 where a.sal < b.sal)6* order by a.sal desc
5000
3000
2975
But what happens when there are only 2 salaries? Since there are not a top 3 then is the answer the null set or just the top 2? Ambiguous question.
2. The poster wanted "to get the top 3 earners"
If you take the emp table and run either answer against it you get 4 rows
SQL> l
1 select distinct a.empno, a.sal
2 from scott.emp a
3 where 3 >= (select count(*)
4 from scott.emp b 5 where a.sal < b.sal)6* order by a.sal desc
7839 5000
7788 3000
7902 3000
7566 2975
This is not the top 3 earners. This is the people who make the top three salaries. Very different. The top three earners in this case are:
( 8i query )
SQL> l
1 select a.empno, a.sal
2 from ( select empno, sal from emp order by sal desc ) a
3* where rownum <= 3
SQL> /
EMPNO SAL
---------- ----------
7839 5000
7788 3000
7902 3000
But what happens when there are 4 people that make the top salary? Your answers returns them all plus all the people who make the next two greatest salaries. My answer will return only 3 rows, leaving one of them out. Both wrong. Again, should the correct answer given the question be the null set since there are not 3 people who are the top earners? I don't know.
My point? The original questions were ambiguous. Given the sample data supplied, you gave a query that retrieved the correct answer, but with real world data, it might not be what the original poster wants.
chris.
>
>He suggests experimenting with the performance of the two, as they may vary
>with indexing and other factors.
>
>
><narana_at_my-deja.com> wrote in message news:7n4fek$qve$1_at_nnrp1.deja.com...
>> How do i get (using sql) the top x salary using the emp table?????
>>
>> eg emp1 salary 10
>> emp2 salary 5
>> emp3 salary 6
>> emp4 salary 8
>> emp5 salary 3
>> emp6 salary 2
>>
>> to get the top 3 earners result will be
>>
>> emp1 salary 10
>> emp4 salary 8
>> emp3 salary 6
>>
>>
>> How can i get this result using sql ????????
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Share what you know. Learn what you don't.
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |