Урок 12.4 · Время чтения: ~7 мин

«Найдите вторую по величине зарплату» — один из самых частых вопросов на собеседовании по SQL. Обычно за ним следуют «а теперь N-ю» и «а теперь в каждом отделе». В этом уроке вы решите задачу пятью способами: MAX с подзапросом, LIMIT ... OFFSET, DENSE_RANK() и коррелированный подзапрос — и увидите ловушки, на которые смотрят интервьюеры: одинаковые зарплаты, отсутствие N-го значения и группы.

N-я по величине зарплата в SQL

Задача кажется простой, и именно поэтому она популярна: первая попытка обычно работает на примере данных и ломается на граничном случае. Хороший ответ учитывает три вещи: дубли (у двух сотрудников одинаковая зарплата), отсутствие ответа (различных зарплат меньше N) и группы (N-я зарплата в каждом отделе).

Примеры используют синтаксис MySQL 8.0 и создают собственную таблицу, поэтому запускайте их в песочнице на базе без учебных данных.

Учебные данные

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employee VALUES
    (1, 'Alice', 'Sales', 5000),
    (2, 'Bob', 'Sales', 7000),
    (3, 'Carol', 'Sales', 7000),
    (4, 'Dave', 'IT', 9000),
    (5, 'Eve', 'IT', 6500),
    (6, 'Frank', 'IT', 9000),
    (7, 'Grace', 'HR', 4800),
    (8, 'Heidi', 'HR', 5200);

Различные зарплаты сверху вниз: 9000, 7000, 6500, 5200, 5000, 4800. У двоих по 9000 и у двоих по 7000, поэтому вторая по величине зарплата — 7000, а не 9000.

Решение 1: максимум ниже максимума

Вторая по величине зарплата — это наибольшая зарплата, которая меньше максимальной:

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Результат: 7000.

Решение короткое, работает в любой СУБД, учитывает дубли и возвращает NULL, если второй зарплаты нет, потому что MAX от пустого множества равен NULL. Ограничение: оно работает только для N = 2. Для третьей зарплаты понадобится ещё один уровень вложенности.

Решение 2: DISTINCT с LIMIT и OFFSET

Отсортируйте зарплаты сверху вниз и пропустите N − 1 из них:

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Результат: 7000.

DISTINCT здесь обязателен. Без него отсортированный список выглядит как 9000, 9000, 7000, …, и OFFSET 1 попадает на вторую 9000:

-- Неверно: из-за дубля снова возвращается максимальная зарплата
SELECT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Результат: 9000 — максимальная зарплата, а не вторая по величине.

Как вернуть NULL, если ответа нет

Если N больше числа различных зарплат, запрос возвращает ноль строк. В задачах на собеседовании часто требуется вернуть NULL. Оберните запрос в скалярный подзапрос: подзапрос, который ничего не нашёл, возвращает NULL.

SELECT (
    SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 9
) AS tenth_highest_salary;

Результат: одна строка с NULL; различных зарплат всего 6.

LIMIT ... OFFSET — синтаксис MySQL, PostgreSQL и SQLite. В SQL Server и Oracle 12c+ используется OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.

Решение 3: DENSE_RANK

Оконная функция ранжирует каждую строку, а внешний запрос выбирает нужный ранг. Это самое гибкое решение, и обычно его ждут на собеседовании:

WITH ranked AS (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT name, salary
FROM ranked
WHERE salary_rank = 2;

Результат: Bob и Carol, оба с зарплатой 7000.

Это решение отвечает и на следующий вопрос — «кто получает вторую по величине зарплату». Замените 2 на любое N. Подходит только DENSE_RANK:

ROW_NUMBER даёт одинаковым зарплатам разные номера, а RANK оставляет пропуски после равных значений, поэтому RANK = 2 не находит никого. DENSE_RANK нумерует различные значения без пропусков.

Решение 4: коррелированный подзапрос

До появления оконных функций классический ответ звучал так: зарплата является N-й по величине, если ровно N различных зарплат больше или равны ей.

SELECT DISTINCT e1.salary
FROM employee e1
WHERE 3 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employee e2
    WHERE e2.salary >= e1.salary
);

Результат: 6500 — третья по величине зарплата.

Это работает в любой СУБД, но подзапрос выполняется для каждой строки, поэтому на большой таблице решение медленное. Упомяните его как альтернативу, а на практике используйте DENSE_RANK.

Решение 5: N-я по величине в каждой группе

Обычный следующий вопрос: «вторая по величине зарплата в каждом отделе». Добавьте в оконную функцию PARTITION BY:

WITH ranked AS (
    SELECT department, name, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT department, name, salary
FROM ranked
WHERE salary_rank = 2
ORDER BY department;

Результат: HR — Grace (4800), IT — Eve (6500), Sales — Alice (5000).

В Sales у Bob и Carol одинаковая максимальная зарплата, поэтому вторая по величине — 5000 у Alice. Отдела, где всего одна различная зарплата, в результате не будет.

Какое решение выбрать

На собеседовании начните с DENSE_RANK, объясните, почему не ROW_NUMBER или RANK, и скажите, как вернуть NULL.

Практика

• Рейтинг зарплат сотрудников
• Самые высокооплачиваемые сотрудники отделов
• Фильмы с наибольшей стоимостью замены

Ключевые выводы:

• Второе по величине значение — это MAX(x) WHERE x < (SELECT MAX(x) ...); запрос учитывает дубли и возвращает NULL, если значения нет.
• С LIMIT 1 OFFSET N-1 всегда используйте DISTINCT, иначе дубли сдвинут ответ.
• Скалярный подзапрос превращает «ноль строк» в NULL.
• DENSE_RANK() решает задачу для любого N, возвращает сотрудников, а с PARTITION BY работает по группам.
• ROW_NUMBER и RANK дают неверный ответ, когда значения повторяются.

Часто задаваемые вопросы

Как найти вторую по величине зарплату в SQL?

Самый короткий переносимый запрос: SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);. Он возвращает наибольшую зарплату ниже максимальной, не зависит от дублей и возвращает NULL, если у всех одинаковая зарплата.

Как найти N-ю по величине зарплату в SQL?

Используйте DENSE_RANK() OVER (ORDER BY salary DESC) в CTE и отфильтруйте ранг N или SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1. Оба способа правильно обрабатывают одинаковые зарплаты.

Почему не ROW_NUMBER или RANK?

ROW_NUMBER нумерует одинаковые зарплаты по-разному, поэтому дубль наверху ставит то же значение на второе место. RANK пропускает номера после равных значений: при двух максимальных зарплатах ранга 2 нет вовсе. DENSE_RANK нумерует различные значения подряд.

Как вернуть NULL, если N-й по величине зарплаты нет?

Поместите запрос в скалярный подзапрос: SELECT (SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 9) AS result;. Когда внутренний запрос ничего не находит, внешний всё равно возвращает одну строку с NULL.