CODEWITHSUNDEEP
sqlsql-serverselectrank
ee8291
ee8291

Reputation: 555

How to retrieve highest salary for each department across employees?

I am trying to compile a query which gives me the highest salary per each department and for each unique employee. The complexity is that 1 employee can be part of multiple departments.

In case the same employee has the highest salary in several departments, only the department with a lower salary should show. This is my start but I am not sure how to continue from here:

select max(salary) as salary, dd.dept_name,d.emp_no
from salaries s
inner join dept_emp d on
s.emp_no=d.emp_no 
inner join departments dd on
d.dept_no=dd.dept_no
group by 2,3;

My output is:
enter image description here

What should I modify from here?

Upvotes: 0

Views: 164

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

For an employee, you seem to only want to include the department with the smallest salary. I would recommend using window functions:

select s.*
from (select s.*,
             rank() over (partition by dept_name order by salary desc) as seqnum_d
      from (select s.*, d.dept_name,
                   rank() over (partition by dept_name order by salary) as seqnum_ed
            from salaries s join
                 dept_emp d
                 on s.emp_no = d.emp_no join
                 departments dd 
                 d.dept_no = dd.dept_no
           ) s
      where seqnum_ed = 1
     ) s
where seqnum_d = 1;

Upvotes: 1

rodrigoap
rodrigoap

Reputation: 7480

Something like this?

select m.salary, m.emp_no, salary.dept_name from salary,
(select emp_no, min(salary) salary from salary group by emp_no) m
where
m.emp_no=salary.emp_no and m.salary=salary.salary;

Upvotes: 0

Related Questions