CODEWITHSUNDEEP
mysqlsql
codeBarer
codeBarer

Reputation: 2378

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.

Upvotes: 1

Views: 114

Answers (4)

beautiful.drifter
beautiful.drifter

Reputation: 311

set @nthMaxSalary = 2;

select ctr,dept,salary from (
select @rowCtr := if(@dept = dept,@rowCtr +1,1) as ctr
,@dept := dept dept
,salary
from dept_salary
order by dept,salary desc
  ) t where ctr = @nthMaxSalary ;

Upvotes: 0

Swapnil Kumbhar
Swapnil Kumbhar

Reputation: 460

select 
 DISTINCT(salary) 
from dept_salary
 order by salary desc 
limit 1,1

Upvotes: 0

Gulmuhammad Akbari
Gulmuhammad Akbari

Reputation: 2036

Try to group by department:

SELECT DEPARTMENT, MAX(SALARY) AS max_salary FROM dept_salary
GROUP BY DEPARTMENT
ORDER BY SALARY DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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

Upvotes: 2

Related Questions