Leetcode 184 - Department Highest Salary

https://leetcode.com/problems/department-highest-salary/

*** SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음

 

SELECT department.name AS Department
     , sub.name AS Employee
     , sub.max_salary AS Salary
FROM( 
    SELECT id 
         , name
         , salary
         , departmentid
         , MAX(salary) OVER (PARTITION BY departmentId) AS max_salary
    FROM employee
    -- WHERE salary = max_salary >>> SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음
    ) as sub
    INNER JOIN Department ON sub.departmentid = department.id
WHERE sub.salary = sub.max_salary