185 Department Top Three Salaries

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

# Write your MySQL query statement below
SELECT d.Name AS Department, se.Name AS Employee, se.Salary 
FROM Department d,
 ( SELECT e.Name, e.DepartmentId, e.Salary,
          @Rank := (CASE 
                    WHEN @PrevDept != e.DepartmentId THEN 1
                    WHEN @PrevSalary = e.Salary THEN @Rank
                    ELSE @Rank + 1 END) AS Rank, 
          @PrevDept := e.DepartmentId,
          @PrevSalary := e.Salary
    FROM Employee e, (SELECT @Rank := 0, @PrevDept := 0, @PrevSalary := 0) r
    ORDER BY DepartmentId ASC, Salary DESC
  ) se
WHERE d.Id = se.DepartmentId AND se.Rank <= 3

 

select D.Name as Department, E.Name as Employee, E.Salary as Salary 
  from Employee E, Department D
   where (select count(distinct(Salary)) from Employee 
           where DepartmentId = E.DepartmentId and Salary > E.Salary) in (0, 1, 2)
         and 
           E.DepartmentId = D.Id 
         order by E.DepartmentId, E.Salary DESC;