Leecode SQL 184. Department Highest Salary 找出tie

184. 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 )