Исполняемые доки SQLite: ORDER BY

Q: Как отсортировать результат запроса в SQLite?

В конец SELECT добавьте ORDER BY и укажите столбец для сортировки. Например, SELECT * FROM users ORDER BY name; отсортирует по возрастанию. Чтобы получить обратный порядок, допишите DESC: ORDER BY name DESC. Без ORDER BY порядок строк не определён — даже если он кажется стабильным, полагаться на это нельзя.

Q: Как сделать сортировку по нескольким столбцам?

Перечислите их через запятую: ORDER BY country, name. SQLite сначала отсортирует по первому столбцу, а второй использует для разрешения «ничьих» — когда значения первого совпадают. Направление можно задать для каждого столбца отдельно: ORDER BY country ASC, signup_date DESC.

Q: Как сортировать без учёта регистра в SQLite?

Используйте COLLATE NOCASE в ORDER BY: ORDER BY name COLLATE NOCASE. По умолчанию SQLite сравнивает строки побайтово, поэтому Zoe окажется раньше apple (заглавные идут до строчных). С NOCASE регистр при сравнении игнорируется.

Без ORDER BY порядок строк не определён

SELECT без ORDER BY возвращает строки в том порядке, который SQLite сочтёт удобным. На маленьких таблицах это часто выглядит как порядок вставки, и многие начинают на это полагаться. Зря. Стоит подключиться индексу, разрастись таблице или измениться плану запроса — и порядок поедет без всякого предупреждения.

Если порядок строк для вас важен — укажите его явно:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country FROM users ORDER BY name;

ORDER BY name по умолчанию сортирует по возрастанию. На выходе всегда получим Ada, Boris, Chen, Rosa — строго по алфавиту, независимо от того, в каком порядке строки лежат в файле базы.

ASC и DESC

ASC — это сортировка по возрастанию (от меньшего к большему, от A до Z, от старого к новому). DESC — наоборот, по убыванию. По умолчанию используется ASC, поэтому его почти всегда опускают:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   '2024-01-05'),
    ('Rosa',  '2024-02-20'),
    ('Chen',  '2024-03-01');

SELECT name, signup_date FROM users ORDER BY signup_date DESC;

Так вы получите самые свежие регистрации первыми. Даты, сохранённые как строки в формате ISO 8601 (YYYY-MM-DD), корректно сортируются как обычный текст — это одна из причин, по которой такой формат предпочитают для столбцов с датами в SQLite, где отдельного типа для дат попросту нет.

Сортировка по нескольким столбцам в SQL

Если по первому столбцу значения совпадают, SQLite разрешает «ничью» по второму. Просто перечислите столбцы через запятую в порядке приоритета:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO users (name, country) VALUES
    ('Boris', 'FR'),
    ('Ada',   'US'),
    ('Rosa',  'FR'),
    ('Chen',  'US');

SELECT name, country FROM users ORDER BY country, name;

Сначала строки группируются по стране (FR идёт перед US), а внутри каждой страны — упорядочиваются по имени. Для каждого столбца можно задать своё направление сортировки:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country, signup_date
FROM users
ORDER BY country ASC, signup_date DESC;

Сначала страна по возрастанию, а внутри каждой страны — от самых свежих к старым. Важный момент: ASC и DESC относятся только к тому столбцу, рядом с которым стоят, и на соседние не распространяются.

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

В ORDER BY можно подставить любое выражение, а не только имя столбца. Это удобно, когда нужно отсортировать по вычисляемому значению:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    quantity INTEGER
);

INSERT INTO products (name, price, quantity) VALUES
    ('Тетрадь',   4.50, 30),
    ('Ручка',     1.20, 100),
    ('Лампа',    25.00, 5);

SELECT name, price * quantity AS revenue
FROM products
ORDER BY revenue DESC;

Псевдоним revenue из списка SELECT спокойно работает в ORDER BY. Можно и переписать выражение целиком — ORDER BY price * quantity DESC — результат будет тот же.

Ещё допустимо сортировать по номеру столбца, но от этой привычки лучше отучаться:

SELECT name, price FROM products ORDER BY 2 DESC;

2 означает второй столбец в списке SELECT. Работает-то оно работает, но стоит кому-нибудь переставить столбцы местами — и смысл сортировки молча поменяется. Лучше сортировать по имени столбца или алиасу.

Куда попадают NULL при сортировке

NULL — это «неизвестно», и SQLite должен как-то решить, куда девать неизвестные значения при сортировке. Правило по умолчанию такое: при ASC значения NULL идут в начале, а при DESC — в конце.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Борис', '2024-03-12'),
    ('Ада',   NULL),
    ('Роза',  '2024-02-20'),
    ('Чен',   NULL);

SELECT name, signup_date FROM users ORDER BY signup_date;

Ada и Chen оказываются в самом верху, ещё до реальных дат. Для сортировки «сначала самые свежие» это, как правило, не то, что нужно. Поведение можно переопределить через NULLS LAST:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users
ORDER BY signup_date DESC NULLS LAST;

Теперь сначала идут реальные даты, а NULL уезжают в конец. NULLS FIRST работает наоборот. Обе конструкции — часть стандарта SQL и поддерживаются в SQLite, начиная с версии 3.30.

Сортировка без учёта регистра через COLLATE NOCASE

По умолчанию SQLite сравнивает строки побайтово — то есть по кодовым точкам Unicode. Из-за этого заглавные буквы оказываются раньше строчных, и 'Zoe' идёт перед 'apple':

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name;

Получаем Boris, Zoe, ada, apple — сначала прописные, потом строчные. Чтобы выполнить сортировку без учёта регистра, добавьте сопоставление NOCASE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name COLLATE NOCASE;

Теперь получаем ada, apple, Boris, Zoe. Учтите: NOCASE считает эквивалентными только ASCII-буквы A–Z и a–z — диакритику и не-ASCII символы он не нормализует. Для полноценной интернационализированной сортировки придётся подключать свою collation на уровне приложения, но для обычных английских строк NOCASE вполне справляется.

Случайный порядок строк

Иногда строки нужны в случайном порядке — например, чтобы выбрать «товар дня» или взять выборку для тестов. В SQLite для этого есть функция random(), которая возвращает случайное целое число; по нему и сортируем:

CREATE TABLE quotes (id INTEGER PRIMARY KEY, text TEXT);

INSERT INTO quotes (text) VALUES
    ('Сначала заставь работать, потом сделай правильно, потом — быстро.'),
    ('Преждевременная оптимизация — корень всех зол.'),
    ('Простое лучше, чем сложное.'),
    ('Читаемость имеет значение.');

SELECT text FROM quotes ORDER BY random() LIMIT 1;

Каждой строке присваивается новое случайное значение, и сортировка их перемешивает. Для небольших таблиц это нормально. А вот на больших ORDER BY random() тормозит — нужно вычислить случайное значение для каждой строки и отсортировать весь результат. Если задача — выбрать одну случайную строку из огромной таблицы, есть способы пошустрее (например, выбрать случайный rowid).

Типичные грабли

Несколько моментов, на которых регулярно спотыкаются:

Забыли ORDER BY и понадеялись на «естественный» порядок. Без него порядок не определён. Даже если кажется стабильным — это иллюзия.
Сортировка чисел, сохранённых как текст. '10' лексикографически идёт раньше '2'. Если столбец должен сортироваться как число, храните его с числовым affinity (или приводите тип явно: ORDER BY CAST(value AS INTEGER)).
Смешивание ASC и DESC по разным столбцам. У каждого столбца своё направление. ORDER BY a, b DESC сортирует a по возрастанию, а b по убыванию — а не оба по убыванию.
Сортировка огромной выборки ради нескольких верхних строк. Связка ORDER BY + LIMIT плюс индекс по столбцу сортировки — об этом как раз в следующей главе.

Дальше: LIMIT и OFFSET

ORDER BY говорит SQLite, как расставить строки; LIMIT и OFFSET — сколько вернуть и с какой позиции начать. Вместе они образуют основу пагинации и запросов вида «топ-N» — об этом дальше.

Часто задаваемые вопросы

Как отсортировать результат запроса в SQLite?

В конец SELECT добавьте ORDER BY и укажите столбец для сортировки. Например, SELECT * FROM users ORDER BY name; отсортирует по возрастанию. Чтобы получить обратный порядок, допишите DESC: ORDER BY name DESC. Без ORDER BY порядок строк не определён — даже если он кажется стабильным, полагаться на это нельзя.

Как сделать сортировку по нескольким столбцам?

Перечислите их через запятую: ORDER BY country, name. SQLite сначала отсортирует по первому столбцу, а второй использует для разрешения «ничьих» — когда значения первого совпадают. Направление можно задать для каждого столбца отдельно: ORDER BY country ASC, signup_date DESC.

Как сортировать без учёта регистра в SQLite?

Используйте COLLATE NOCASE в ORDER BY: ORDER BY name COLLATE NOCASE. По умолчанию SQLite сравнивает строки побайтово, поэтому Zoe окажется раньше apple (заглавные идут до строчных). С NOCASE регистр при сравнении игнорируется.

Где в отсортированном результате окажутся NULL-значения?

По умолчанию NULL идут в начале при сортировке по возрастанию и в конце — при сортировке по убыванию. Поведение можно переопределить через NULLS FIRST или NULLS LAST. Например, ORDER BY signup_date DESC NULLS LAST покажет сначала свежие даты, а пустые значения отправит в самый низ.

Связанные концепции

ORDER BY в SQLite: сортировка ASC, DESC и по нескольким полям