Given the above table definitions, write a SQL query to find employees who earn the top three salaries in each of the department.
Here we need to use Dense_Rank() function that will basically rank the employees based on salary after partitioning with department ID.
Dense_Rank() over(partition by E.DepartmentId order by E.salary desc) as Rank
Then we can use subquery to get only top three earners.
Select Department,Employee,salary
from (
select D.Name as Department, E.name as Employee, E.salary,
Dense_Rank() over(partition by E.DepartmentId order by E.salary desc) as Rank
from employee E join Department D
on E.DepartmentId =D.Id
) as topearners where Rank <=3