184. Department Highest Salary

Table: Employee
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee.
It also contains the ID of their department.
 

Table: Department
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.

Write a solution to find employees who have the highest salary in each of the departments.
Return the result table in any order.
The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and
Henry has the highest salary in the Sales department.

 

-- 풀이
with max_salary as (
  select departmentId
       , max(salary) as salary
    from Employee
    group by departmentId
)

select d.name as Department
     , e.name as Employee
     , e.salary as Salary
  from Employee e
  left join Department d
    on e.departmentId = d.id
  where (e.departmentId, e.salary) in (select * from max_salary)

 

- 해설

크게 2단계 과정을 밟으면 된다.

1. 부서별 id와 max salary를 출력하는 쿼리문을 서브쿼리로 만듦.

2. Employee 테이블에서 위의 서브쿼리 출력 결과와 일치하는 데이터만 추출. 

 

위처럼 하지 않고 그냥 Employee table과 Department tabel을 조인해서 max salary를 출력하면, 전혀 다른 출력하게 된다. 한 부서 내에 highest salary를 받는 사람이 2명 이상인 경우, 그들을 모두 출력해야 하기 때문.

 

물론 위의 풀이 말고 다른 풀이도 당연히 있을 것이다.

예를 들어, 부서 별 salary 별로 랭킹을 매겨서 1등을 출력한다거나(여기선 row_number가 아닌 dense_rank를 써야 할 것이다) 하는 등 여러가지 방법이 있을 것 같다.