Сортировка данных в SQL: оператор ORDER BY

Оператор ORDER BY в SQL сортирует строки результата запроса по одному или нескольким столбцам, по возрастанию или по убыванию. Ниже разобраны синтаксис, сортировка по выражениям и своему порядку, поведение NULL и текста, связка с LIMIT и GROUP BY. Все примеры выполнены в SQLite, в конце показано, как задать сортировку из Python-кода.

Синтаксис и место в запросе

ORDER BY пишется после WHERE, GROUP BY и HAVING, но перед LIMIT. Логически сервер сортирует уже отобранные строки, поэтому в ORDER BY можно ссылаться на псевдонимы столбцов из SELECT.

SELECT столбцы
FROM таблица
WHERE условие
ORDER BY столбец1 [ASC | DESC], столбец2 [ASC | DESC]
LIMIT n;

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

Для примеров возьмем таблицу сотрудников:

CREATE TABLE employees (
    id     INTEGER PRIMARY KEY,
    name   TEXT,
    dept   TEXT,
    salary INTEGER
);
INSERT INTO employees (name, dept, salary) VALUES
    ('Анна', 'IT', 120000), ('Борис', 'Sales', 80000),
    ('Вера', 'IT', 95000),  ('Глеб', 'Sales', NULL),
    ('Дина', 'HR', 80000);

ASC, DESC и несколько столбцов

Ключевое слово ASC задает сортировку по возрастанию и используется по умолчанию, его можно не писать. DESC сортирует по убыванию. Направление указывается для каждого столбца отдельно: в записи ORDER BY a, b DESC по убыванию сортируется только b.

SELECT name, salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC, name;
-- Анна   120000
-- Вера    95000
-- Борис   80000
-- Дина    80000

Второй столбец работает как правило разрешения ничьих: Борис и Дина получают одинаковую зарплату, и между ними решает сортировка по имени. Без второго столбца порядок равных строк может меняться от запуска к запуску. Если строки выводятся постранично, добавляйте в конец уникальный столбец, например id, иначе одна и та же строка может попасть на две страницы.

Сортировка по выражениям и своему порядку

В ORDER BY допустимо любое выражение: арифметика, функции, псевдоним вычисляемого столбца. Можно указать и порядковый номер столбца в списке SELECT, например ORDER BY 2, но такой запрос легко сломать, переставив столбцы, поэтому лучше писать имена.

SELECT name, salary * 12 AS year_pay
FROM employees
WHERE salary IS NOT NULL
ORDER BY year_pay DESC, LENGTH(name)
LIMIT 2;
-- Анна  1440000
-- Вера  1140000

Произвольный порядок через CASE

Когда нужен порядок, которого нет в данных, например отделы в определенной последовательности, его задают выражением CASE. Каждому значению присваивается число, по нему и идет сортировка.

SELECT name, dept FROM employees
ORDER BY CASE dept
             WHEN 'IT' THEN 1
             WHEN 'HR' THEN 2
             ELSE 3
         END,
         name;
-- Анна IT, Вера IT, Дина HR, Борис Sales, Глеб Sales

NULL, регистр и числа в тексте

Где окажется NULL, зависит от СУБД. В SQLite и MySQL пустое значение считается меньше любого другого, поэтому при ASC оно идет первым, а при DESC последним. В PostgreSQL и Oracle наоборот: при ASC значения NULL уходят в конец. Чтобы не зависеть от базы, укажите место явно. SQLite понимает такую запись с версии 3.30, PostgreSQL давно.

SELECT name, salary FROM employees
ORDER BY salary DESC NULLS FIRST;
-- Глеб NULL, Анна 120000, Вера 95000, ...

Текст сортируется посимвольно по правилу сравнения (collation). По умолчанию в SQLite используется двоичное сравнение, при котором заглавные латинские буквы идут раньше строчных. Модификатор COLLATE NOCASE убирает разницу в регистре, но только для латиницы: для кириллицы он не работает, и русские строки нужно приводить к одному регистру в Python или подключать свою функцию сравнения.

Еще одна ловушка: числа, сохраненные как текст, сортируются лексикографически, и '100' окажется раньше '9'. Исправляется приведением типа: ORDER BY CAST(price AS INTEGER). Подробнее о том, как SQLite хранит значения, рассказано в статье про типы данных SQLite.

ORDER BY вместе с LIMIT и GROUP BY

Связка ORDER BY и LIMIT дает выборку "топ-N": самые дорогие товары, последние заказы, лучшие результаты. Параметр OFFSET пропускает заданное число строк и используется для постраничного вывода.

-- третья страница по 20 записей, новые сверху
SELECT id, name FROM employees
ORDER BY id DESC
LIMIT 20 OFFSET 40;

После группировки сортировать можно по агрегатной функции. Запрос ниже считает среднюю зарплату в каждом отделе и выводит отделы от самого высокого среднего к низкому. Сам принцип группировки описан на странице про оператор GROUP BY.

SELECT dept, AVG(salary) AS avg_pay, COUNT(*) AS people
FROM employees
GROUP BY dept
ORDER BY avg_pay DESC;
-- IT     107500.0  2
-- Sales   80000.0  2
-- HR      80000.0  1

Обратите внимание: у Sales и HR одинаковое среднее, и порядок между ними не определен. Функция AVG() пропускает NULL, поэтому для Sales среднее посчитано по одному сотруднику, а COUNT(*) учитывает обоих.

Сортировка из Python и скорость

При работе через модуль sqlite3 значения в WHERE передаются параметрами ?. Имя столбца и направление сортировки так передать нельзя: плейсхолдер подставляет значение, а не идентификатор. Поэтому имя столбца, пришедшее от пользователя, проверяют по списку разрешенных и только потом вставляют в текст запроса.

import sqlite3

ALLOWED = {"name", "salary", "dept"}

def list_employees(conn, sort_by="name", desc=False):
    if sort_by not in ALLOWED:
        raise ValueError(f"Нельзя сортировать по {sort_by!r}")
    direction = "DESC" if desc else "ASC"
    sql = f"SELECT name, salary FROM employees ORDER BY {sort_by} {direction}, id"
    return conn.execute(sql).fetchall()

conn = sqlite3.connect("company.db")
print(list_employees(conn, "salary", desc=True)[:2])
# [('Анна', 120000), ('Вера', 95000)]

Сортировка большой таблицы требует памяти и времени. Если по столбцу часто сортируют, создайте на нем индекс: CREATE INDEX idx_emp_salary ON employees(salary);. Тогда база читает строки уже в нужном порядке, и запрос с LIMIT отдает первые записи почти мгновенно. Проверить, используется ли индекс, можно командой EXPLAIN QUERY PLAN: строка USE TEMP B-TREE FOR ORDER BY означает, что база сортирует данные отдельно. Работа с базой из Python подробно разобрана в статье Python sqlite.

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

Короткие ответы на вопросы о сортировке, которые часто задают новички.

Можно ли сортировать по столбцу, которого нет в SELECT?

Да, в обычном запросе можно сортировать по любому столбцу таблицы, даже если он не выводится. Исключение составляет SELECT DISTINCT: в большинстве СУБД столбец сортировки должен входить в список выборки.

Как вывести строки в случайном порядке?

В SQLite и PostgreSQL пишут ORDER BY RANDOM(), в MySQL ORDER BY RAND(). На больших таблицах это медленно, так как случайное число вычисляется для каждой строки и затем вся выборка сортируется.

Влияет ли ORDER BY в подзапросе на итоговый порядок?

Нет, стандарт SQL не гарантирует, что порядок из подзапроса сохранится во внешнем запросе. Сортировку указывают в самом внешнем SELECT.

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

Если дата и время хранятся в разных столбцах, перечислите оба: ORDER BY day DESC, time DESC. Если в одном столбце в формате ISO вроде 2024-05-01 14:30, достаточно сортировать по нему, такие строки упорядочиваются правильно и как текст.