TheEmployeetable 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 |
+----+-------+--------+--------------+
TheDepartmenttable 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 |
+------------+----------+--------+
Solution 1
SELECT
D.Name AS Department,
E.Name AS Employee,
E.Salary
FROM
Employee AS E,
(SELECT DepartmentId, MAX(Salary) AS max FROM Employee GROUP BY DepartmentId) AS T,
Department AS D
WHERE E.DepartmentId = T.DepartmentId
AND E.Salary = T.max
AND E.DepartmentId = D.id
Solution 2
SELECT
Department.name AS Department,
Employee.name AS Employee,
Salary
FROM
Employee
JOIN Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
Solution 3
SELECT
D.Name AS Department,
E1.Name AS Employee,
E1.Salary AS Salary
FROM
Department AS D
JOIN Employee AS E1 ON D.Id = E1.DepartmentId
WHERE E1.Salary = (
SELECT MAX(E2.Salary)
FROM Employee AS E2
WHERE E2.DepartmentId = E1.DepartmentId
);
Solution 4
SELECT Department.Name Department, e1.Name Employee, e1.Salary
FROM employee e1
LEFT JOIN employee e2 ON e1.DepartmentId = e2.DepartmentId AND e1.Salary < e2.Salary
JOIN Department ON e1.DepartmentId = Department.Id
WHERE e2.Salary is null