Top 5 salary for each department 2

[JeanneZ]

Hi, SQL experts,
I need help. I want to like top 5 employees with highest salary for each department. For example, we have 3 departments A, B, and C. I would like to have:

Deparment A:
emp1 $100,000
emp2 $99,000
emp3 $98,200
emp4 $89,999
emp5 $85,300

Deparment B:
emp6 $120,000
emp7 $100,900
emp8 $98,000
emp9 $97,000
emp10 $89,900

Deparment C:
emp11 $45,000
emp12 $35,999
emp13 $35,900
emp14 $34,000
emp15 $30,000

Thanks a lot for any help.

 

[SantaMufasa]

JeanneZ,

Your need is the "Poster Girl" for Oracle Analytic Functions. Here is example of code that does what you want:

select dept_id, name, salary, rnk
  from (select dept_id, last_name name, salary
              , rank() over (partition by dept_id order by salary desc) rnk
          from s_emp)
 where rnk <= 5
 order by dept_id,salary desc
/

If you need further explanation of how this works, let us know and we can post some good links or explain personally.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JeanneZ]

I am sorry. I gave wrong examples. I wanted to count how many employees with top salary range.
For example,

Department A:
$100,000 1 employee
$90,000 1 employee
$80,000 3 employees
$70,000 6 employees
$60,000 10 employees

Deparment B:
$40,000 10 employees
%38,000 5 employees
...

Department C:
....

Thanks a lot for any help.

 

[SantaMufasa]

In that case, I would probably use this code (which does not involve Oracle Analytic Functions):

break on dept_id
col counts format a13
col x heading "Annual|Salary" format $999,999
select dept_id, range*10000 x
      ,count(*)||' employee'||decode(count(*),1,null,'s') Counts
  from (select dept_id, trunc(salary/10000) range 
          from s_emp)
 group by dept_id, range
 order by dept_id, range desc;

           Annual
DEPT_ID    Salary COUNTS
------- --------- -----------
     10  $140,000 1 employee
     31  $140,000 2 employees
     32  $140,000 1 employee
     33  $150,000 1 employee
     34  $150,000 1 employee
          $70,000 1 employee
     35  $140,000 1 employee
     41  $140,000 2 employees
         $120,000 1 employee
          $90,000 1 employee
     42  $120,000 2 employees

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

If you think that Santa Dave has helped you, then "Thank him" by giving him a star!!

Bill
Oracle DBA/Developer
New York State, USA

 

[JeanneZ]

Mufasa,
Thanks a lot for your help.

 

[SantaMufasa]

Thanks for "having my back", Bill. As you know, I try to encourage

-giving, as well, for helpful posts (when I am not the

beneficiary).

And thanks, too, Jeanne, for the

. I'm happy you found things helpful.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]