Топ-N по группам

Топ-N в каждой группе — например, два самых высокооплачиваемых сотрудника в каждом отделе.

«Топ-N по группам» решается оконной функцией: пронумеровать строки внутри группы и взять первые N.

Почему GROUP BY тут не хватает

GROUP BY + MAX даст только одну вершину на группу и потеряет имена. А нам нужно несколько строк из каждой группы с деталями. Это работа для оконной функции ROW_NUMBER с PARTITION BY.

Решение через ROW_NUMBER

Нумеруем сотрудников внутри отдела по убыванию зарплаты и оставляем тех, у кого номер ≤ N:

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

WITH ranked AS (
    SELECT name, dept, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS n
    FROM emp
)
SELECT dept, name, salary
FROM ranked
WHERE n <= 2                 -- топ-2 в каждом отделе
ORDER BY dept, salary DESC;

Вывод:

HR|Гена|85000
HR|Дина|80000
IT|Аня|120000
IT|Борис|110000

В IT топ-2: Аня и Борис (Вера с 90000 отсеяна). В HR: Гена и Дина (Женя выбыл). Меняя n <= 2 на любое N, получаем топ-N. Это эталонное решение задачи.

ROW_NUMBER vs RANK при ничьей

Тонкость: если на границе N есть одинаковые значения, ROW_NUMBER произвольно разорвёт ничью (возьмёт ровно N строк), а RANK/DENSE_RANK оставят всех с подходящим рангом (может выйти больше N строк). Выбор зависит от того, что считать «топом» при ничьей:

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

WITH ranked AS (
    SELECT name, salary,
           RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS r
    FROM emp
)
SELECT name, salary, r
FROM ranked
WHERE r <= 2                 -- при ничьей на 2-м месте останутся оба
ORDER BY salary DESC, name;

Вывод:

Аня|120000|1
Борис|100000|2
Вера|100000|2

Борис и Вера делят 2-е место (по 100000). RANK дал обоим ранг 2, и условие r <= 2 оставило обоих — итого 3 строки в «топ-2». Если нужно строго N строк — берите ROW_NUMBER.

Итог

Топ-N по группам: ROW_NUMBER() OVER (PARTITION BY группа ORDER BY ...), фильтр n <= N.
GROUP BY сюда не подходит — он сворачивает группу и теряет детали строк.
При ничьих на границе: ROW_NUMBER даёт ровно N, RANK может вернуть больше.