Department Highest Salary [ MSSQL ]

Department Highest Salary - LeetCode

WINDOW FUNCTION을 사용해 departmentId 기준으로 그룹을 묶어 max_salary값을 구한 후

FROM절 서브쿼리를 사용해 max_salary와 같은 salary를 갖고있는 row만 추출했습니다.

SELECT
      Department
    , Employee
    , Salary
FROM (
    SELECT
        d.name AS Department
      , e.name AS Employee
      , e.salary AS Salary
      , MAX(e.salary) OVER(PARTITION BY e.departmentId) AS max_salary
    FROM
        employee e
    INNER JOIN
        department d
    ON
        e.departmentId = d.id
) AS ms
WHERE
    ms.Salary = ms.max_salary;