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 =

Department =

Output

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 )