Department highest salary

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

Department Highest Salary - LeetCode

SQL 문제

처음 짠 쿼리 

SELECT Top.Name AS Department, E1.Name AS Employee, E1.Salary
FROM Employee AS E1
INNER JOIN (SELECT D.Name, E.DepartmentId, MAX(Salary) AS Salary
FROM Employee AS E
INNER JOIN Department AS D ON D.Id = E.DepartmentId
GROUP BY D.Name) AS Top ON Top.DepartmentId = E1.DepartmentId AND Top.Salary = E1.Salary

 

윈도우 함수 MAX() 적용 쿼리

SELECT D.Name AS Department
     , l.Name AS Employee
     , l.Salary AS Salary
FROM (
    SELECT DepartmentId
         , Name
         , Salary
         , MAX(Salary) OVER (PARTITION BY DepartmentId) AS MAXSalary
    FROM Employee) l
INNER JOIN Department AS D ON D.id = l.DepartmentId
WHERE l.Salary = l.MAXSalary

 

확실히 원도우 함수를 사용하여 쿼리를 짜는 것이 쉽게만 느껴진다. 이 문제는 나중에 RNAK()나 DENSE_RANK()로도 다시 한 번 풀 생각