SQL: Найти N-ую самую высокую зарплату

Решение

Задача и контекст

Нужно найти N-ую самую высокую уникальную зарплату. Это важно различать:

• N-ая самая высокая → уникальные значения (1, 2, 3, ...)
• N-ый наибольший → с учётом дубликатов

В примере уникальные зарплаты: 100000 (Alice, Diana), 90000 (Charlie), 80000 (Bob), 75000 (Eve)

• 1-ая (самая высокая): 100000
• 2-ая: 90000
• 3-ая: 80000
• 4-ая: 75000

Решение 1: С использованием LIMIT + OFFSET (простое и быстрое)

SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- N-1 для получения N-ой зарплаты

Для N = 2:

SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- Результат: 90000

Объяснение:

• DISTINCT — исключаем дубликаты зарплат
• ORDER BY salary DESC — сортируем по убыванию
• LIMIT 1 — берём одну строку
• OFFSET N-1 — пропускаем первые N-1 строк

Преимущества:

• Самый простой синтаксис
• Быстро выполняется
• Понимается всеми

Недостатки:

• Если N > количество уникальных зарплат → NULL (нужно обработать)
• Не очень гибкий для сложных аналитических запросов

Решение 2: С использованием DENSE_RANK (для продвинутого анализа)

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = 2;

Как это работает:

employee.salary | DENSE_RANK()
100000          | 1  (Alice)
100000          | 1  (Diana)  ← одинаковая, одинаковый ранг
90000           | 2  (Charlie)
80000           | 3  (Bob)
75000           | 4  (Eve)

• DENSE_RANK() — в отличие от RANK(), не пропускает ранги при дубликатах
• OVER (ORDER BY salary DESC) — окно по всем строкам, сортировка по убыванию
• WHERE rank = 2 — получаем строки с рангом 2

Разница между RANK и DENSE_RANK:

Rank():       DENSE_RANK():
100000  → 1   100000  → 1
100000  → 1   100000  → 1
90000   → 3   90000   → 2  ← не пропускает 2
80000   → 4   80000   → 3
75000   → 5   75000   → 4

Решение 3: Гибридный подход (параметризированный N)

DECLARE @N INT = 2;

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = @N;

Для разных БД:

PostgreSQL:

PREPARE get_nth_salary (INT) AS
SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = $1;

EXECUTE get_nth_salary(2);

MySQL:

SET @N = 2;

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = @N;

Решение 4: С использованием LEAD (для сравнения зарплат)

SELECT salary
FROM (
    SELECT
        salary,
        LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary
    FROM Employee
) t
WHERE prev_salary IS NULL OR salary != prev_salary
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Как это работает:

LAG(salary) показывает предыдущую зарплату
100000  → NULL (первая)
100000  → 100000 (такая же, скипаем)
90000   → 100000 (отличается, это новый уровень)
80000   → 90000 (отличается)
75000   → 80000 (отличается)

Отфильтровываем: NULL и salary != LAG → берём только новые уровни

Решение 5: Обработка NULL при N > кол-ва уникальных зарплат

DECLARE @N INT = 5;  -- Запрос на 5-ую, но уникальных только 4

SELECT
    COALESCE(
        (
            SELECT salary
            FROM (
                SELECT
                    salary,
                    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
                FROM Employee
            ) ranked
            WHERE rank = @N
        ),
        NULL  -- или дефолтное значение
    ) AS nth_highest_salary;

Результат: NULL

Альтернатива с сообщением об ошибке:

SELECT
    CASE
        WHEN (
            SELECT COUNT(DISTINCT salary)
            FROM Employee
        ) < @N
        THEN 'N exceeds number of unique salaries'
        ELSE (
            SELECT salary
            FROM (
                SELECT
                    salary,
                    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
                FROM Employee
            ) ranked
            WHERE rank = @N
        )
    END AS result;

Решение 6: Для получения информации о сотруднике с N-ой зарплатой

SELECT
    id,
    name,
    salary,
    department_id,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employee
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 2;

Примечание: В стандартном SQL нельзя использовать оконные функции в WHERE. Используем подзапрос:

SELECT
    id,
    name,
    salary,
    department_id,
    rank
FROM (
    SELECT
        id,
        name,
        salary,
        department_id,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = 2;

Результат:

id | name    | salary | department_id | rank
---|---------|--------|---------------|----- 
3  | Charlie | 90000  | 2             | 2

Сравнение решений

Рекомендуемое решение для production

CREATE FUNCTION getNthHighestSalary(@N INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @result DECIMAL(10,2);
    
    SELECT @result = salary
    FROM (
        SELECT
            salary,
            DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
        FROM Employee
    ) ranked
    WHERE rank = @N;
    
    RETURN @result;
END;

-- Использование
SELECT dbo.getNthHighestSalary(2) AS second_highest;

Тест на примере

Запрос:

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = 2;

Выполнение:

employee:       rank calculation:       filtering (rank=2):
100000 (Alice)  →  rank=1              
100000 (Diana)  →  rank=1              
90000 (Charlie) →  rank=2  ✓ ← результат
80000 (Bob)     →  rank=3              
75000 (Eve)     →  rank=4              

Результат: 90000

Итоговый чеклист

• Используй DENSE_RANK() для N-ой самой высокой уникальной зарплаты
• OFFSET/LIMIT для простых случаев
• Оборачивай в подзапрос для фильтрации по окну
• Обработай случай N > кол-ва уникальных значений
• Для production используй параметризованные функции
• Помни: RANK() пропускает, DENSE_RANK() не пропускает номера
• На больших таблицах индекс на salary улучшит производительность