codeBarer codeBarer - 3 years ago 129
SQL Question

How to find the nth max salary from different departments?

I'm trying find the nth largest salary for every department from the list. I am able to do the basic min and max using aggregation e.g.

Select DEPARTMENT, MAX(SALARY) FROM dept_salary
GROUP BY 1


Using MySQL I'm able to get nth salary based on individual but not combined:

SELECT DISTINCT DEPARTMENT, SALARY FROM dept_salary
ORDER BY 2 DESC
LIMIT n,1

DEPARTMENT SALARY
-------------------------
Customer Service 143937
Human Resources 141953
Customer Service 138637
Customer Service 137535
Customer Service 136548
Customer Service 135650
Marketing 135497
Customer Service 134893
Customer Service 133837
Customer Service 133569


Any pointer on how to get this to work is kindly appreciated.

Answer Source

For the general solution, variables are the simplest method:

select ds.*
from (select ds.*,
             (@rn := if(@d = department, @rn + 1,
                        if(@d := department, 1, 1)
                       )
             ) as rn
      from (select distinct department, salary
            from dept_salary
           ) ds cross join
           (select @rn := 0, @d := '') params
      order by department, salary desc
     ) ds
where rn = 3;  -- for instance
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download