184 Department Highest Salary

Description

Table: Employee

• 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

• 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.

Problem Statement

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:

• Department table:

Output:

Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

Solution

There are two ways to solve this problem. If you’re comfortable with window operations, you can use window function to solve it in elegant manner. Alternatively, you can get max salary for each department using subquery and then retrieve the employees with highest salary for each department by using results of the subquery.

Approach 1: Using RANK Window Function

The problem can be divided into two parts.

1. Find department for each employee. This can be done using JOIN operation with Department table. The join condition can be Employee.departmentId = Department.id.
2. For each department, find the employee with highest salary. This requires that we partition the data by department. Here we also want to order the results by salary in descending order. So, you must include that in the window clause. Once we have partitioned the data per department, you can use RANK or DENSE_RANK window function to assign ranks to each employee based on their salary.

1WITH cte AS (
2    SELECT e.name Employee, e.salary Salary, d.name Department,
3        RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) rnk
4        FROM Employee e
5        JOIN Department d
6        ON e.departmentId = d.id
7) SELECT Department, Employee, Salary
8    FROM cte
9    WHERE rnk=1;

Approach 2: Using Aggregation and Subquery

You first join the tables Employee and Department using JOIN opeartion. Next, you can find the highest salary per department using subquery.

1SELECT departmentId, MAX(salary) FROM Employee
2    GROUP BY DepartmentId;

Now, you can filter records where the department and salary matches the results of the subquery.

1SELECT d.name Department,
2    e.name Employee,
3    e.salary Salary
4    FROM Employee e JOIN Department d
5    ON e.departmentId = d.id
6    WHERE (e.departmentId, e.salary) IN (
7        SELECT departmentId, MAX(salary) FROM Employee
8        GROUP BY DepartmentId
9    );