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  |
+------------+----------+--------+

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# 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

 

?

1 2 3 4 5 6 7

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;