Find the 3rd Maximum Salary for each department based on table data

Question

I need to find out the 3rd maximum salary for an employee for each department in a table. if no 3rd maximum salary exists then display 2nd maximum salary. if no 2nd maximum salary exist then find the highest salary. How to achieve this result in sql-server?

The table structure is given below

create table employee1(empid int, empname varchar(10), deptid int, salary money)

insert into employee1
select 1,'a',1, 1000
union
select 1,'b',1, 1200 
union
select 1,'c',1, 1500 
union
select 1,'c',1, 15700 
union
select 1,'d',2, 1000 
union
select 1,'e',2, 1200 
union
select 1,'g',3, 1500 

I have tried the common way of getting the maximum salary for each category using row_number function.

;with cte
as
( 
select ROW_NUMBER( ) over( partition by deptid order by salary) as id, * from employee1 
)
select * from cte

Solution 1

Select EmpID,empname,deptid,salary
 From (
Select *
      ,RN  = Row_Number() over (Partition By deptid Order By Salary)
      ,Cnt = sum(1) over (Partition By deptid)
 From  employee1
      ) A
 Where RN = case when Cnt<3 then Cnt else 3 end

Returns

Solution 2

The answer will depend if you want ties and how to handle them. If you want no ties and even if one employee ties with another it becomes the next highest salary then the trick is to use a row_number like the one you are showing only with descending on salary and then use another row_number to reverse it. If you did not want to use row_number a second time you could do it with a few other techniques as well but step 1 is find highest step 2 is to reverse that order

; WITH cteRankSalariesByDepartment AS (
    SELECT
       *
       ,RowNum = DENSE_RANK() OVER (PARTITION BY deptid ORDER BY salary DESC)
    FROM
       employee1
)

, cteReverseRankHighestSalaries AS (
    SELECT
       *
       ,RowNum2 = DENSE_RANK() OVER (PARTITION BY deptid ORDER BY RowNum DESC)
    FROM
       cteRankSalariesByDepartment
    WHERE
       RowNum <= 3
)

SELECT *
FROM
    cteReverseRankHighestSalaries
WHERE
    RowNum2 = 1

Per your comment updated to DENSE_RANK() you could simply use it in place of row_number() and you will get your ties.

Solution 3

Just you query needs count and row_number with condition as below:

;with cte
as
( 
select ROW_NUMBER( ) over( partition by deptid order by salary desc) as id, 
    Cnt = count(*) over(partition by deptid), * from employee1 
)
select * from cte where ( cnt >= 3 and id = 3 )
or ( cnt < 3 and id = 1 )

Solution 4

You could use UNION

;with cte
as
( 
select ROW_NUMBER( ) over( partition by deptid order by salary) as id, * from employee1 
)

--get the 3rd highest
select 
   * 
from cte
where id = 3 

union 

--get the highest / max
select 
   c.*
from cte c
--this determines the highest which salary for each dept
inner join
   (select deptid, max(id) id 
    from cte 
    group by deptid) x on x.deptid = c.deptid and x.id = c.id
--this limits it on depts that aren't in the list in the first part of the query
where 
   c.deptid not in (select deptid from cte where id = 3)

Solution 5

you can try a query like below:

select * from 
(
select 
 empid, 
 empname ,
 deptid ,
 salary ,
 ROW_NUMBER( ) over( partition by deptid order by id desc) as rev_id
from
( 
select 
 ROW_NUMBER( ) over( partition by deptid order by salary) as id, 
 empid, 
 empname ,
 deptid ,
 salary 
from employee1 
)
t where id<=3 
)t where rev_id=1

working demo

View more solutions

Comments

[Matt]

What if there are ties? do you want both records that tie? And in that case what if 1 person has highest salary and 2 people are tied for second do you then want the 3rd highest salary (4th person) or one of the second?

[bmsqldev]

yeah..i would change row_number to dense_rank to address the ties.

[Matt]

I updated the answer with ties you simply need to replace the row_number() with dense_rank()

[Matt]

like the count trick hadn't thought of that. but it doesn't work if there are ties and use DENSE_RANK() to get them instead of ROW_NUBMER(). But I definitely like will have to add that to my arsenal :)

[John Cappelletti]

@Matt Correct, to my shame, I never even considered ties. :-\

[Matt]

The answer for dept 1 for the 3rd highest salary would be empid 1 & 2 at 1200.00. you have your ORDER BY reversed on your ctes but when you fix then you are presenting the answer I have already posted

[Radim Bača]

It returns completely wrong results in certain cases: rextester.com/OTTA88582

[Matt]

@RadimBača I'm not sure if you commented on the wrong answer but your statement and example are both not relevant to this answer. In your example you have your order by in correct and you are using row_number instead of dense rank for ties. First you have to order by salary descending which would put the highest salary(ies) as row 1 then you sort by ascend salary which puts the lowest of those as row 1 because rownum can reasonably be used as a surrogate as long as you use it descending that works too

[Radim Bača]

sorry I made a mistake, your answer is ok.