Leetcode: 184. the highest paid employee in the Department

The original title is ：https://leetcode-cn.com/problems/department-highest-salary/

Personal solution :

$$benefitusewindowmouthLetterCount：$$

select Department.Name as Department, e1.Name as Employee, e1.Salary
from (
    select *
    from (
        select *, dense_rank() over(partition by DepartmentId order by Salary Desc) as ranking
        from Employee
    ) as temp
    where temp.ranking = 1
) as e1
left join Department
on e1.DepartmentId = Department.Id
where Department.Id IS NOT NULL; 

Be careful Department Special cases without any data in the table , Therefore, it needs to be judged Department.Id IS NOT NULL;

Official solution :

$$brightspot:twoindividualwordparagraphOneriseIN$$

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
	)
;