Skip to Main Content
  • Questions
  • Top N salaries but department with hightest overall salary should be top/first

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Anuradha.

Asked: August 25, 2017 - 6:38 am UTC

Last updated: August 25, 2017 - 4:00 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Top N salaries but department with hightest overall salary should be top , then dept with 2nd highest in the dept (with rank 1) should come next .
How do I achieve this SQL query ?

Here is sample data :
create table emp_sal_test ( dept# number ,
emp_name varchar2(200),
salary   number ) 
/

insert into emp_sal_test values ( 40 , 'Isha',80000);
insert into emp_sal_test values ( 40 , 'Pooja',70000);
insert into emp_sal_test values ( 40 , 'Geet',77000);
insert into emp_sal_test values ( 10 , 'Julie',75000);
insert into emp_sal_test values ( 10 , 'Jia',69000);
insert into emp_sal_test values ( 10 , 'Diya',72000);
insert into emp_sal_test values ( 20 , 'Sanvi',74000);
insert into emp_sal_test values ( 20 , 'Siya',65000);
insert into emp_sal_test values ( 20 , 'Seeta',81000);

The result of the query should be as following :
DEPT# EMP    SALARY 

20 Seeta 81000 
20 Sanvi 74000 
20 Siya 65000 

40 Isha 80000 
40      Geet    77000
40 Pooja 70000 

10 Julie 75000 
10 Diya 72000 
10 Jia 69000 



Thanks
Anu


and Chris said...

First you need to find the maximum salary in each department.

You can find this by using the analytic version of max(), partitioning by department. e.g.:

select e.*, max(salary) over (partition by e.dept#) dept_mx_sal 
from   emp_sal_test e;

DEPT#  EMP_NAME  SALARY  DEPT_MX_SAL  
10     Jia       69000   75000        
10     Diya      72000   75000        
10     Julie     75000   75000        
20     Sanvi     74000   81000        
20     Seeta     81000   81000        
20     Siya      65000   81000        
40     Geet      77000   80000        
40     Pooja     70000   80000        
40     Isha      80000   80000


All you need to do now is order by this descending, then the salaries desc:

select e.*, max(salary) over (partition by e.dept#) dept_mx_sal 
from   emp_sal_test e
order  by dept_mx_sal desc, salary desc

DEPT#  EMP_NAME  SALARY  DEPT_MX_SAL  
20     Seeta     81000   81000        
20     Sanvi     74000   81000        
20     Siya      65000   81000        
40     Isha      80000   80000        
40     Geet      77000   80000        
40     Pooja     70000   80000        
10     Julie     75000   75000        
10     Diya      72000   75000        
10     Jia       69000   75000 

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what about collisions?

Thomas Brotherton, August 25, 2017 - 1:21 pm UTC

Shouldn't you include dept# in the order by, in case two departments had the same max salary?
order  by dept_mx_sal desc, dept#, salary desc

Chris Saxon
August 25, 2017 - 4:00 pm UTC

Good question... Depends on whether the depts should be kept separate or if all departments with the same max sal should be treated as one.

Very quick and correct

Anuradha Kulkarni, August 25, 2017 - 8:01 pm UTC

Exact query what I was looking for , Thanks a lot !

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.