Re: SQL

From: Glen Siferd <siferd_at_admin.uwex.edu>
Date: Wed, 21 Jul 1999 09:41:01 -0500
Message-ID: <7n4m5u$cbg$1@news.doit.wisc.edu>

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;

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.
Received on Wed Jul 21 1999 - 09:41:01 CDT