The database has a Salary table in which each employee has an ID, as well as a column for the department ID.
Write an SQL query to find the employees who receive the three highest salaries in each department.
Salary:
Id Name Salary DepartmentId
+----+-------+--------+----
| 1 | Petr | 85000 | 1 |
| 2 | Ivan | 80000 | 2 |
| 3 | Alex | 60000 | 2 |
| 4 | Den | 90000 | 1 |
| 5 | Bob | 69000 | 1 |
| 6 | Kir | 85000 | 1 |
| 7 | Mike | 76000 | 1 |
Department:
(DepartmentId, DepartmentName)
1 IT
2 Sales
The SQL query should return the following rows (the order of the rows does not matter):
Department Employee Salary
+-----------+----------+-----------+
| IT | Den | 90000 |
| IT | Petr | 85000 |
| IT | Kir | 85000 |
| IT | Mike | 76000 |
| Sales | Ivan | 80000 |
| Sales | Alex | 60000 |
here is my query:
SELECT d.name as Department,s1.name as Employee,s1.salary
FROM salary s1
left join department d on s1.departmentid=d.id
WHERE (SELECT COUNT(DISTINCT s2.salary)
from Department d2
WHERE s2.salary>s1.salary and s2.departmentid=s1.departmentid)<3
ORDER BY d.name,s1.salary DESC;
would like to solve with a window function and CTE
>Solution :
dense_rank() returns the rank of each row within a result set partition, with no gaps in the ranking values.
dense_rank() give the same rank to duplicated rows (exemple salary 85000 both will have order 2)
rn <= 3 with this condition we control how many number of highest salaries should be taken :
with cte as (
select *, dense_rank() over (partition by DepartmentId order by Salary desc) as rn
from Salary
)
select d.DepartmentName as Departement, Name as employee, Salary
from cte c
inner join Department d on d.DepartmentId = c.DepartmentId
where rn <= 3
order by c.departmentid, rn