Ранжирование: ROW_NUMBER, RANK, DENSE_RANK

Фильтрация топ-N строк по рангу через CTE

Допустим, нужно получить три заказа с наибольшей суммой в каждой категории. Первый порыв — написать WHERE DENSE_RANK() OVER (...) <= 3 прямо в том же SELECT. Это не работает: SQL вычисляет WHERE до того, как оконные функции получают доступ к данным, и интерпретатор отклонит такой запрос с ошибкой.

Решение — вынести ранжирование в CTE, а фильтр поставить во внешнем SELECT:

WITH ranked AS (
    SELECT
        order_id,
        category,
        total_amount,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY total_amount DESC
        ) AS dr
    FROM orders
)
SELECT order_id, category, total_amount, dr
FROM ranked
WHERE dr <= 3;

CTE ranked — это именованный подзапрос. Внутри него оконная функция выполняется по всему набору строк orders и кладёт готовый ранг в столбец dr. Внешний SELECT уже видит dr как обычный числовой столбец и может фильтровать по нему без ограничений.

Почему DENSE_RANK, а не RANK? Если в категории два заказа с одинаковой максимальной суммой, RANK отдаст им оба ранг 1, а следующему — ранг 3. При фильтре <= 3 мы всё равно вернём нужные строки, но логика рванёт при другом граничном значении: например, при двух равных на 3-м месте RANK = 3 вернёт обоих, а RANK = 4 уже не существует, и следующая строка имеет ранг

5. DENSE_RANK держит последовательность плотной, поэтому <= 3 стабильно означает «первые три уникальные позиции по значению».

Если же требуется ровно три строки без исключений — подойдёт ROW_NUMBER() <= 3, но тогда при равных суммах победитель выбирается произвольно. Выбор функции зависит от того, что важнее: стабильное число строк или честное отражение равенства позиций.