Department Top Three Salaries

Write a SQL query to find employees who earn the top three salaries in each of the departments.

Example

Table: Employee                             Table: Department

+----+-------+--------+--------------+      +----+----------+

| Id | Name  | Salary | DepartmentId |      | Id | Name     |

+----+-------+--------+--------------+      +----+----------+

| 1  | Ram   | 85000  | 1            |      | 1  | IT       |

| 2  | Henry | 80000  | 2            |      | 2  | Sales    |

| 3  | Sam   | 60000  | 2            |      +----+----------+

| 4  | Shyam | 60000  | 1            |

| 5  | Geeta | 90000  | 1            |

| 6  | Sheet | 90000  | 1            |

| 7  | Leela | 80000  | 1            |

| 8  | Geeta | 70000  | 1            |

+----+-------+--------+--------------+

Result: 

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT         | Geeta    | 90000  |

| IT         | Sheet    | 90000  |

| IT         | Ram      | 85000  |

| IT         | Leela    | 80000  |

| Sales      | Henry    | 80000  |

| Sales      | Sam      | 60000  |

+------------+----------+--------+

Approach

Mysql

SELECT d.Name as Department, t.Name as Employee, t.Salary 

FROM

(SELECT  e1.Name  , e1.Salary  , e1.DepartmentId 

FROM 

    Employee e1 

    JOIN Employee e2 

WHERE 

    e1.DepartmentId = e2.DepartmentId 

    AND e1.Salary <= e2.Salary            

GROUP BY e1.id             

HAVING COUNT(DISTINCT(e2.Salary)) <= 3 ) as t

JOIN Department d on d.id=t.DepartmentId;

Oracle

SELECT t.Department, t.Name Employee,

t.Salary 

FROM

(

    SELECT e.Name, e.Salary, d.Name AS Department,

    DENSE_RANK() OVER (PARTITION BY e.DepartmentId 

    ORDER BY e.Salary DESC) AS dense_rank

    FROM Employee e, Department d

    WHERE e.DepartmentId = d.Id

) t

WHERE dense_rank <=3;