[Stratascratch/SQL] Highest Paid Employee By Department

https://platform.stratascratch.com/coding/9854-find-the-5-highest-salaries?code_type=3

 

 

# 풀이1
select department, worker_id, salary
from (select *, rank() over(partition by department order by salary desc) as rnk
	  from worker) as cte
where rnk = 1;

# 풀이2
select worker_id, department, salary
from(
	select *, max(salary) over(partition by department) as max_dep
    from worker) as cte
where salary = max_dep

partition by란?

(참고: https://project-notwork.tistory.com/53)

	GROUP BY	PARTITION BY
사용	그룹 외부에서 묶어 순위 및 그룹별 집계를 구할 떄 사용	그룹 내 순위 및 그룹별 집계를 구할 때 사용
결과값	특정 원하는 컬럼에 대해서 추출해 결과값 보여줌	전체 데이터에서 원하는 결과값 보여줌