Типичная задача: найти X по группам

«Найди максимальную зарплату в каждом отделе» решается одной строкой с GROUP BY. «Найди сотрудника с максимальной зарплатой в каждом отделе» уже нет, и на этой разнице интервьюер проверяет, понимаешь ли ты, что возвращает группировка. Задачу называют «наибольший в группе», и у неё несколько решений. Запросы урока работают на учебной базе блока.

Как спрашивают

Выведи максимальную зарплату в каждом отделе. А теперь выведи имя сотрудника, который её получает. Что будет, если у двух сотрудников одинаковая зарплата?

Короткий ответ

Максимум по группе это GROUP BY с max. Строку с максимумом одной группировкой не достать: после GROUP BY от группы остаётся одна строка, и имя в неё не поставить. Нужен второй шаг: найти максимум и вернуться к таблице за строками, у которых значение равно максимуму. Это делают подзапросом по паре (отдел, зарплата), коррелированным подзапросом или DISTINCT ON в PostgreSQL. При равных значениях первые два способа вернут всех, а DISTINCT ON одного.

Разбор: рекорды класса

Учитель знает, что лучший результат по прыжкам в 7А равен 4 метрам. Чтобы наградить ученика, ему мало числа «4 метра»: он открывает журнал и ищет, у кого записано 4 метра. Если таких двое, грамоту получают оба. Так и устроены решения ниже: сначала число, потом поиск строк с этим числом.

Шаг первый, максимум по отделам:

SELECT department_id, max(salary) AS max_salary
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY department_id;

department_id | max_salary
--------------+-----------
            1 |     300000
            2 |     200000
            3 |     120000

Добавить сюда name нельзя, мы разбирали почему в уроке про GROUP BY.

Способ 1: подзапрос по паре значений

SELECT name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (
  SELECT department_id, max(salary)
  FROM employees
  GROUP BY department_id
)
ORDER BY department_id;

name | department_id | salary
-----+---------------+-------
Анна |             1 | 300000
Глеб |             2 | 200000
Егор |             3 | 120000

Подзапрос возвращает пары «отдел, максимум», а внешний запрос оставляет сотрудников, у которых пара совпала.

Способ 2: коррелированный подзапрос

SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
  SELECT max(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

Результат тот же. Подзапрос здесь коррелированный: он ссылается на e.department_id внешнего запроса и логически считается для каждого сотрудника заново. Подробнее о таких подзапросах в уроке про подзапросы.

Способ 3: DISTINCT ON в PostgreSQL

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id, salary DESC, name;

DISTINCT ON (department_id) оставляет из каждой группы первую строку в порядке ORDER BY. Сортировка внутри группы по salary DESC ставит наверх самого высокооплачиваемого. Это расширение PostgreSQL, в других СУБД его нет, и там то же самое делают через ROW_NUMBER(), о нём урок про ранжирование.