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

Классическая задача: найти максимум в каждой группе — и достать строку, где он достигается.

«Найти X по каждой группе» — это сначала агрегат по группам, а затем (часто) возврат к строкам, где этот X достигнут.

Простой случай: само значение

Если нужно только число — например, максимальная зарплата в каждом отделе — хватает GROUP BY с MAX.

CREATE TABLE emp (
    id     INTEGER PRIMARY KEY,
    name   TEXT,
    dept   TEXT,
    salary INTEGER
);
INSERT INTO emp (name, dept, salary) VALUES
    ('Аня',  'IT', 120000),
    ('Борис','IT',  95000),
    ('Вера', 'HR',  80000),
    ('Гена', 'HR',  90000);

SELECT dept, MAX(salary) AS max_zarplata
FROM emp
GROUP BY dept
ORDER BY dept;

Вывод:

HR|90000
IT|120000

IT — 120000, HR — 90000. Просто и понятно, пока нам не нужно ещё и имя того, кто столько получает.

Сложный случай: кто именно?

Каверза в том, что нельзя просто добавить name в SELECT — это негруппировочная колонка (см. урок про GROUP BY). Нужно найти строку, где зарплата равна максимуму по отделу. Классический приём — коррелированный подзапрос:

CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp (name, dept, salary) VALUES
    ('Аня','IT',120000), ('Борис','IT',95000),
    ('Вера','HR',80000),  ('Гена','HR',90000);

-- Сотрудник(и) с максимальной зарплатой в своём отделе
SELECT e.dept, e.name, e.salary
FROM emp e
WHERE e.salary = (
    SELECT MAX(e2.salary) FROM emp e2 WHERE e2.dept = e.dept
)
ORDER BY e.dept;

Вывод:

HR|Гена|90000
IT|Аня|120000

Для каждой строки подзапрос считает максимум по её отделу и сравнивает. Остаются только «чемпионы»: Аня (IT) и Гена (HR). Бонус: при равенстве зарплат вернутся все такие сотрудники — это часто и нужно.

Альтернатива: JOIN со свёрнутой таблицей

Тот же результат через соединение с агрегированной подтаблицей — нагляднее для многих:

CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp (name, dept, salary) VALUES
    ('Аня','IT',120000), ('Борис','IT',95000),
    ('Вера','HR',80000),  ('Гена','HR',90000);

SELECT e.dept, e.name, e.salary
FROM emp e
JOIN (
    SELECT dept, MAX(salary) AS ms FROM emp GROUP BY dept
) t ON t.dept = e.dept AND t.ms = e.salary
ORDER BY e.dept;

Вывод:

HR|Гена|90000
IT|Аня|120000

Подзапрос t заранее считает максимум по отделам, а JOIN оставляет только совпадающие строки. В разделе про оконные функции мы увидим ещё более лаконичный способ через ROW_NUMBER.

Итог

«Само значение по группам» — обычный GROUP BY + MAX/MIN/SUM.
«Какая именно строка даёт этот экстремум» — коррелированный подзапрос или JOIN со свёрнутой таблицей.
Оба способа возвращают все строки при равенстве — это часто желаемое поведение.