- Department Highest Salary
注意！要找出 tie 的 highest salary！
Write a solution to find employees who have the highest salary in each of the departments.
Return the result table in any order.
The result format is in the following example.
Input
Employee =
| id | name | salary | departmentId |
|---|---|---|---|
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
Department =
| id | name |
|---|---|
| 1 | IT |
| 2 | Sales |
Output
| Department | Employee | salary |
|---|---|---|
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
My wrong solution (didn’t find the tie):
-- WITH a AS (
-- SELECT departmentId AS di, name, MAX(salary) AS highest
-- FROM employee e
-- GROUP BY e.departmentId
-- )
-- SELECT d.name AS Department, a.name AS Employee, a.highest AS Salary
-- FROM a
-- JOIN department d
-- ON a.di = d.id
Correct solution by others:
SELECT dp.name AS Department, em.name AS Employee, em.salary
FROM Employee AS em
JOIN Department as dp
ON em.departmentId = dp.id
WHERE em.salary = (SELECT MAX(salary) FROM Employee
WHERE departmentId = dp.id )