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

user_5359841 0
Viewed 16685

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
tsql sql-server sql
6 Answers

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.

user_55106270 
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

enter image description here

user_15700000

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

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

user_11232260

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

To increase your question I added two employees with the same salary in the third position.

To get this you need the first dense_rank the salary by department. After you need to reverse l the salary rank and get the position 1

try it

    DECLARE @employee1 TABLE 
      ( 
         empid   INT, 
         empname VARCHAR(10), 
         deptid  INT, 
         salary  MONEY 
      ) 

    INSERT @employee1 Values 
    (1,'a',1, 1000  )
,(1,'b',1, 1200 )
,(2,'bb',1, 1200 )
,(1,'c',1, 1500 )
,(3,'ccc',1, 1500 )
,(1,'c',1, 15700) 
,(1,'d',2, 1000 )
,(1,'e',2, 1200 )
,(1,'g',3, 1500 ) 

    WITH cte_rank 
         AS (SELECT Dense_rank() 
                      OVER ( 
                        partition BY deptid 
                        ORDER BY salary) SalaryRank, 
                    * 
             FROM   @employee1), 
         cte_final 
         AS (SELECT Dense_rank() 
                      OVER ( 
                        partition BY deptid 
                        ORDER BY salaryrank DESC) SalaryRankReverse, 
                    * 
             FROM   cte_rank 
             WHERE  salaryrank <= 3) 
    SELECT * 
    FROM   cte_final 
    WHERE  salaryrankreverse = 1

Result

SalaryRankReverse    SalaryRank           empid       empname    deptid      salary
-------------------- -------------------- ----------- ---------- ----------- ---------------------
1                    3                    1           c          1           1500.00
1                    3                    3           ccc        1           1500.00
1                    2                    1           e          2           1200.00
1                    1                    1           g          3           1500.00
user_67016980
Related