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
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