ORDER BY в SQL: сортировка результата и DESC

ORDER BY задаёт порядок строк в результате. Без него порядок не определён вообще: база вправе вернуть строки как ей удобнее, и сегодняшний ответ ничего не обещает про завтрашний. Это не придирка, а источник самых неприятных багов в отчётах. Всё дальше снято на PostgreSQL 16.11.

Данные те же, что в остальных статьях справочника:

users

orders

Два заказа на 1200 стоят здесь не случайно: на этой паре держится главный раздел статьи.

SQL / 01koddo

При равных суммах нужен второй ключ

Как отсортировать результат: ASC и DESC

По возрастанию сортируют по умолчанию, слово ASC писать не обязательно:

SELECT id, amount FROM orders ORDER BY amount, id;

DESC разворачивает порядок:

SELECT id, amount FROM orders ORDER BY amount DESC, id;

Второй ключ id в обоих запросах стоит не для красоты. Он нужен затем, чтобы две строки по 1200 всегда шли в одном и том же порядке, и почему без него они разъезжаются, разобрано ниже.

Как сортировать по нескольким колонкам

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

SELECT u.city, o.amount, o.id
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY u.city, o.amount DESC, o.id;

Города идут по возрастанию, суммы внутри города по убыванию. Чтобы развернуть оба ключа, DESC пишут дважды:

SELECT u.city, o.amount, o.id
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY u.city DESC, o.amount DESC, o.id;

Ошибка «поставил DESC один раз, а развернулось не всё» живёт именно здесь. Каждая колонка получает своё направление, умолчание всегда ASC.

ORDER BY по номеру колонки и по алиасу

Вместо имени можно поставить номер колонки в списке SELECT, нумерация с единицы:

SELECT status, count(*) AS cnt
FROM orders
GROUP BY status
ORDER BY 2 DESC, 1;

Двойка означает count(*), единица означает status. Тот же запрос через алиас читается лучше и переживает правку списка колонок:

SELECT status, count(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC, status;

Результат совпадает. Разница вылезает при редактировании: добавишь колонку в начало SELECT, и номера молча начнут указывать на другое, а имена нет. Ровно та же ловушка описана для группировки в статье про GROUP BY, и вывод общий: номер уместен в разовом запросе в консоли, в сохранённом отчёте пиши имя.

Номер вне списка колонок база отвергает сразу:

SELECT id, amount FROM orders ORDER BY 5;

ERROR:  ORDER BY position 5 is not in select list
LINE 1: SELECT id, amount FROM orders ORDER BY 5;
                                               ^

Можно ли сортировать по колонке, которой нет в SELECT

Да. ORDER BY работает по строкам таблицы, а не по колонкам вывода:

SELECT name FROM users ORDER BY city, id;

В выдаче одни имена, а порядок задан городом, которого в ней нет. Так же можно сортировать по агрегату, не показывая его:

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY sum(o.amount) DESC, u.name;

Клиенты выстроены по сумме заказов, самой суммы в отчёте нет. Исключение из этой свободы одно: с SELECT DISTINCT сортировать разрешено только по колонкам выборки, и почему так, разобрано в статье про DISTINCT.

Где ORDER BY стоит в запросе

После HAVING, перед LIMIT. Синтаксический порядок предложений такой: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. Не путай его с логическим порядком обработки, в котором FROM и фильтры идут перед вычислением списка SELECT. Поставишь сортировку раньше фильтра, получишь синтаксическую ошибку:

SELECT id FROM orders ORDER BY id WHERE amount > 1000;

ERROR:  syntax error at or near "WHERE"
LINE 1: SELECT id FROM orders ORDER BY id WHERE amount > 1000;
                                          ^

Практическое следствие важнее синтаксического: ORDER BY выполняется после всех фильтраций и группировок, то есть сортирует уже готовый результат. Отсюда и свобода использовать в нём алиасы из SELECT, которых, например, HAVING не видит. Разбор этой асимметрии есть в статье про GROUP BY и HAVING.

Почему буквы сортируются не по алфавиту

Порядок текста задаёт не SQL, а правило сравнения строк, collation. Оно живёт в настройках базы, и разные правила дают разный алфавит на одних и тех же данных:

SELECT w FROM (VALUES ('Яблоко'),('арбуз'),('Ананас'),('ёж'),('Ежевика'),('банан')) t(w)
ORDER BY w COLLATE "C";

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

SELECT w FROM (VALUES ('Яблоко'),('арбуз'),('Ананас'),('ёж'),('Ежевика'),('банан')) t(w)
ORDER BY w COLLATE "ru-RU-x-icu";

Регистр перестал влиять, «ё» встала на своё место рядом с «е». Популярный самодельный обход через lower() проблему решает наполовину:

SELECT w FROM (VALUES ('Яблоко'),('арбуз'),('Ананас'),('ёж'),('Ежевика'),('банан')) t(w)
ORDER BY lower(w);

Регистр он убрал, а «ёж» всё равно оказался после «Яблока»: у буквы «ё» код выше, чем у «я», и байтовое сравнение об алфавите не знает. Плюс lower() мешает использовать обычный индекс. Когда порядок важен для пользователя, задавай collation явно, а не подгоняй его функциями.

Куда попадают пустые значения

В PostgreSQL NULL считается больше любого значения, поэтому при ASC пропуски уезжают в конец:

SELECT id, nullif(amount, 1200) AS amount FROM orders ORDER BY 2, id;

Место пропусков задаётся явно через NULLS FIRST и NULLS LAST, а умолчания у разных баз противоположные, так что при переезде с MySQL тот же отчёт начнётся с другого конца; замеры на обоих движках лежат в статье про NULL.

Почему один и тот же запрос возвращает разный порядок

Потому что ORDER BY обещает порядок только по своим ключам. Строки, равные по всем ключам, база вправе выдать в любом порядке, и на практике этот порядок диктует план запроса.

Сначала доведём таблицу до состояния, в котором это заметно, и хватит для этого обычной правки. Команда UPDATE orders SET status = 'paid' WHERE id = 12 для уже оплаченного заказа не меняет ровным счётом ничего по смыслу, но переносит строку в конец файла: PostgreSQL пишет новую версию строки, а старую помечает мёртвой. Физический порядок в таблице сместился, и теперь полное чтение встречает заказ 13 раньше заказа 12.

Дальше один и тот же запрос по двум заказам на 1200, отсортированный только по сумме, но выполненный двумя разными способами:

SELECT id, amount FROM orders ORDER BY amount DESC LIMIT 3;

При полном чтении таблицы с последующей сортировкой:

При чтении по индексу на amount в обратную сторону:

Заказы 12 и 13 поменялись местами. Данные одни и те же, запрос тот же, отличается только план, и ORDER BY amount обе выдачи считает одинаково верными. Отчёт перевернулся молча, без ошибки и без предупреждения, а поводом стала правка, которая не изменила ни одного значения.

Лечится одной строкой. Добавь в конец ключей что-нибудь заведомо уникальное:

SELECT id, amount FROM orders ORDER BY amount DESC, id LIMIT 3;

Теперь ответ один при любом плане. Особенно это важно для постраничного вывода: LIMIT 20 OFFSET 20 без уникального ключа в сортировке умеет показать одну строку на двух страницах подряд и потерять другую совсем. Пользователь увидит дубль, а в логах не будет ни ошибки, ни намёка.

Сколько стоит сортировка

Отдельным шагом плана, если помочь базе нечем:

EXPLAIN (COSTS OFF) SELECT id, amount FROM orders ORDER BY amount;

 Sort
   Sort Key: amount
   ->  Seq Scan on orders

Индекс по колонке сортировки убирает этот шаг целиком, потому что индекс уже хранит значения по порядку:

 Index Scan using orders_amount_idx on orders

Узел Sort пропал, база просто идёт по индексу. Выигрыш заметен на больших таблицах и особенно с LIMIT: вместо сортировки миллиона строк ради первых двадцати читаются ровно двадцать. Порядок колонок в составном индексе должен совпадать с порядком ключей в ORDER BY, иначе он не сработает. Как читать такие планы и когда индекс бесполезен, разобрано в статье про индексы в PostgreSQL.

На чём ловят на собеседовании

«Без ORDER BY строки идут в порядке вставки». Ни в каком порядке. На маленькой таблице так часто и выходит, а после первого же UPDATE или смены плана порядок меняется.

«DESC разворачивает всю сортировку». Только свою колонку. ORDER BY city, amount DESC сортирует города по возрастанию.

«ORDER BY 1 и ORDER BY имя_колонки эквивалентны». По результату да, по надёжности нет: номер переезжает на другую колонку при правке SELECT и делает это молча.

«Сортировать можно только по тому, что есть в SELECT». Можно по любой колонке таблицы и даже по агрегату. Ограничение появляется только у SELECT DISTINCT.

«ORDER BY внутри подзапроса задаёт порядок внешнего запроса». Нет. Внешний запрос вправе его проигнорировать, и обычно так и делает. Порядок задаётся во внешнем ORDER BY. Исключение — оконные функции, где ORDER BY внутри OVER определяет не вывод, а порядок вычисления окна.

«Сортировка бесплатна, это же просто вывод». Это отдельный узел плана с расходом памяти. На больших объёмах сортировка выплёскивается на диск, и запрос замедляется в разы.

Частые вопросы

Как отсортировать по убыванию

Дописать DESC после имени колонки: ORDER BY amount DESC. Для каждой колонки направление указывается отдельно.

Как отсортировать по дате от новых к старым

ORDER BY created_at DESC. Если в один день бывает несколько записей, добавь вторым ключом id, иначе порядок внутри дня не гарантирован.

Почему ORDER BY не работает в подзапросе

Работает, но результат не обязан сохраниться при обращении к подзапросу снаружи. Сортировку ставят в самый внешний запрос. Исключение — ORDER BY вместе с LIMIT внутри подзапроса: там он определяет, какие строки вообще отберутся.

Можно ли отсортировать по условию

Да, выражением: ORDER BY (status = 'paid') DESC, amount DESC поднимет оплаченные наверх. В ORDER BY разрешено любое выражение, включая CASE.

Где потренироваться

В пути «SQL для аналитиков» на Koddo сортировка идёт сквозной темой: топ-N по сумме, порядок внутри группы, стабильная выдача для постраничного вывода. Собранные вместе сюжеты лежат в задачах по SQL с решениями, а проверить себя на живой задаче с автопроверкой можно на второй по величине зарплате: там ответ целиком зависит от того, как отсортированы одинаковые значения.