Классика: вторая по величине зарплата

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

«Вторая по величине зарплата» — задача-проверка: важно учесть дубликаты (две одинаковые максимальные — это всё ещё одно «место») и пустой результат.

Подвох задачи

«Вторая зарплата» — это не вторая строка после сортировки, а второе уникальное значение. Если максимум получают двое, вторая по величине — это следующее меньшее значение, а не повтор максимума. Покажем разные подходы на данных, где максимум дублируется.

Способ 1: подзапрос с MAX

Идея: «максимум среди тех, кто меньше общего максимума». Коротко и без оконных функций:

CREATE TABLE emp (id INTEGER PRIMARY KEY, salary INTEGER);
INSERT INTO emp (salary) VALUES (120000), (120000), (100000), (90000);

SELECT MAX(salary) AS vtoraya
FROM emp
WHERE salary < (SELECT MAX(salary) FROM emp);

Вывод:

100000

Общий максимум 120000 (его получают двое), а максимум среди меньших — 100000. Это и есть вторая по величине. Дубликат максимума не помешал.

Способ 2: DISTINCT + ORDER BY + LIMIT/OFFSET

Берём уникальные значения, сортируем по убыванию и пропускаем первое. OFFSET 1 LIMIT 1 = «вторая строка»:

CREATE TABLE emp (id INTEGER PRIMARY KEY, salary INTEGER);
INSERT INTO emp (salary) VALUES (120000), (120000), (100000), (90000);

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

Вывод:

100000

DISTINCT убирает дубль 120000, сортировка ставит значения по убыванию, OFFSET 1 пропускает первое (максимум), LIMIT 1 берёт следующее — 100000. Способ читаемый, но LIMIT/OFFSET — это про N-ю строку, помните про DISTINCT, иначе посчитаете дубли.

Способ 3: DENSE_RANK

Самый масштабируемый, легко обобщается на N-ю зарплату. DENSE_RANK присваивает уникальным значениям места без пропусков — берём место 2:

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

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

Вывод:

Вера|100000

Здесь DENSE_RANK даёт обоим стотысячникам место 1, значению 100000 — место 2. Меняя mesto = 2 на любое N, получаем N-ю по величине. И заметьте: способ вернул имя сотрудника, а не только число.

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

Идея на чистом SQL без оконных функций: «зарплата, выше которой ровно одно уникальное значение». Полезно знать для собеседований, где оконные функции «нельзя»:

CREATE TABLE emp (id INTEGER PRIMARY KEY, salary INTEGER);
INSERT INTO emp (salary) VALUES (120000), (120000), (100000), (90000);

SELECT DISTINCT salary AS vtoraya
FROM emp e
WHERE (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e2.salary > e.salary) = 1;

Вывод:

100000

Для зарплаты 100000 выше неё ровно одно уникальное значение (120000) — условие выполнено. Обобщается на N-ю: условие = N-1.

Итог

• «Вторая зарплата» — это второе уникальное значение, а не вторая строка.
• Способы: MAX среди меньших; DISTINCT + LIMIT/OFFSET; DENSE_RANK = 2; коррелированный подзапрос со счётчиком.
• DENSE_RANK легко обобщается на N-ю и умеет вернуть саму строку, а не только число.