'Find the third-highest salary in each department

I am trying to find the third-highest salary in each department if there is such.

enter image description here

SELECT DepartmentID
FROM Employees
GROUP BY DepartmentID

This is what I can do.

I looked at similar posts but not sure I understand how to do it with my table.

sql-serverdatabase


Solution 1:[1]

You can use row_number function to assign a order of salary, then get the 3rd one:

SELECT s.DepartmentID, s.Salary
FROM (
SELECT DepartmentID, Salary, ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS salary_rank
FROM Employees) s
WHERE s.salary_rank=3

Solution 2:[2]

SELECT   e.first_name,
         e.last_name,
         d.department_name,
         salary,
         ROW_NUMBER() OVER ( PARTITION BY d.id ORDER BY salary DESC ) AS 
         salary_rank 
FROM     department d 
JOIN     employee e ON d.id = e.department_id 
ORDER BY department_name;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 PeterHe
Solution 2 eren

Related Questions