185. Department Top Three Salaries

Problem

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
문제링크

Answer

leetcode에서는 윈도우 함수를 쓸 때 MS SQL SERVER로 바꿔서 풀어야함

SELECT c.Department -- 그냥 department 써줘도됨
     , c.Employee
     , c.Salary
FROM (SELECT d.name AS Department
     , e.name AS Employee
     , e.Salary
     , DENSE_RANK() OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC) as dr
-- Partition과 Order의 순서가 바뀌면 안됨,select절안에 있기 때문에 where 절에 조건(top3)을 써줄 수 없다.-> 서브쿼리로 바꿔줘야함
     FROM employee AS e
         INNER JOIN Department AS d 
            ON e.DepartmentId = d.ID
      ) c
WHERE c.dr <= 3 -- =< 이렇게 쓰면 Run안됨