Re: SQL question

From: nijlpaard <nijlpaard_at_mail.com>
Date: Wed, 8 Nov 2000 14:24:15 GMT
Message-ID: <G3pMnu.LBC_at_ahisinfr.xs4all.nl>

xdba_at_my-deja.com heeft geschreven in bericht <8trlqn$srn$1_at_nnrp1.deja.com>...
>Given an employees table like this,
>how could I query the top three salaried people per department inSQL?
>
>Dept Name Salary
>1 Smith 1000
>1 Jones 2000
>1 White 1000
>1 Black 3000
>1 Red 1000
>1 Blue 1000
>1 Smith 5000
>2 Apple 1000
>2 Pear 3000
>2 Peach 1000
>2 Lemon 4000
>2 Melon 2000
>2 Peach 3000
>2 Prune 1000
>etc.

[Quoted] Using inline views solves this problem quite easy. Although we're not able [Quoted] to order in inline views before Oracle 8 we can distinguish the top 3 salaried people by using nested inline views and the group by function.

The first inline view is used for distinguishing the best salary (top1_sal) [Quoted] for each department. In the second inline view we select the second best salary (top2_sal); in the third inline view we select the third best salary [Quoted] (top3_sal). By comparing the salaries with these inline views we get the desired result without using explicit ordering, rownums or functions.

The statement below wil answer your question!

Good luck,

Nellie

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

select t1.dept
     , t1.name
     , t1.salary
  from employees t1
     , (select t2.dept
             , max(t2.salary) top3_sal
          from employees t2
             , (select t3.dept
                     , max(t3.salary) top2_sal
                  from employees t3
                     , (select t4.dept
                             , max(t4.salary) top1_sal
                          from employees t4
                         group by t4.dept) t5
                 where t5.dept = t3.dept
                   and t3.salary < t5.top1_sal
                 group by t3.dept) t6
                 where t2.dept = t6.dept
                   and t2.salary < t6.top2_sal
           group by t2.dept) t7

 where t1.dept = t7.dept
   and t1.salary >= t7.top3_sal Received on Wed Nov 08 2000 - 15:24:15 CET