Find the 3rd maximum salary for each department based on table data

Question

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

The table structure is shown 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 tried to use the general way to get the maximum salary for each category using the row_number function.

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

Answer 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

Answer 2

The answer will depend on whether connections are needed and how to handle them. If you do not want any connections, and even if one employee contacts another, he becomes the next highest salary, then the trick should use row_number , like the one you show only when lowering the salary, and then use another row_number to cancel it. If you didn’t want to use row_number a second time, you could do it with a few other methods, but step 1 is to find the highest step 2 to reverse this 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 

In your comment, updated to DENSE_RANK() , you can simply use it instead of row_number() and you will get your links.

Answer 3

You just ask for count and row_number with a condition as shown 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 ) 

Answer 4

you can try the following query:

 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 demonstration

Answer 5

You can 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) 

Answer 6

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

To get this, you will need the first dense_rank salary department. After you need to cancel the salary rating and get a position 1

try

  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