Написано: 26.03.2023

184. Самая высокая зарплата в департаменте(Department Highest Salary)

medium

SQL Schema.

Table: employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key column for this table.
departmentId is a foreign key 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 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.

Задание.

Напишите SQL-запрос, чтобы найти сотрудников с самой высокой зарплатой в каждом из отделов.

Верните таблицу результатов в любом порядке.

Формат результата запроса приведен в следующем примере.

Пример 1.

Входные данные:

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 |
+----+-------+

Результат:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+

Объяснение: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

Решение.

/* Write your PL/SQL query statement below */
SELECT d.name AS department, e.name AS employee, e.salary FROM employee e
INNER JOIN department d ON d.id = e.departmentId WHERE (salary, departmentId) IN (
    SELECT MAX(salary), departmentId FROM employee GROUP BY departmentId)