184. Department Highest Salary (LeetCode)

Department Highest Salary - LeetCode

Write an SQL query to find employees who have the highest salary in each of the departments.

---

풀이

제출한 답

select d.name as department, e.name as employee, e.salary as salary
from employee as e join department as d on e.departmentid = d.id
where (e.departmentid, e.salary) in (select departmentid, max(salary)
                                 from employee
                                 group by departmentid)

-> where 절 서브쿼리 사용하여 조건 부여

다른 답

select sub.department, sub.employee, sub.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 as e join department as d on e.departmentId = d.id
) sub
where sub.salary = sub.max_salary

-> (MS SQL) window 함수 사용하여 최대값을 포함하는 테이블을 구하고 from 절 서브쿼리 사용

2022.09.18