Порядок и ограничение: ORDER BY, LIMIT, OFFSET

ORDER BY: порядок по требованию

Первый урок заканчивался неприятной новостью: порядок строк в таблице не определён. База вправе вернуть их как ей удобно, и полагаться на «обычно они идут по номеру» нельзя. Настало время получить порядок, который никуда не денется.

SELECT title, price
FROM products
ORDER BY price DESC

Пятнадцать строк, от самого дорогого товара к самому дешёвому:

ORDER BY пишется после WHERE и содержит колонку, по которой строить порядок. Направлений два:

Сортировать можно по любому типу. Числа идут по величине, текст — по алфавиту, даты — по времени (в нашей базе они текстом в формате «год-месяц-день», и порядок совпадает).

И главное отличие ORDER BY от всего, что было раньше: он выполняется после SELECT. Отсюда единственное послабление в языке — в нём можно пользоваться псевдонимом:

SELECT title, price * 0.9 AS skidka
FROM products
ORDER BY skidka DESC

В WHERE так написать было нельзя — там псевдонима ещё не существовало. Здесь он уже есть, и это не исключение из правил, а прямое следствие порядка выполнения.

И сразу мелочь, которая экономит спор: сортировка не отбирает. Строк в ответе ровно столько же, сколько было до неё, — меняется только их очерёдность. Отсекает строки WHERE, ограничивает число LIMIT, а ORDER BY не делает ни того, ни другого.

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

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

Несколько колонок и своё направление у каждой

Одной колонки часто мало. После первой через запятую пишут вторую, и она вступает в дело только там, где первая дала одинаковые значения.

SELECT name, city, age
FROM customers
ORDER BY city, age

Сначала города по алфавиту; внутри каждого города — возраст по возрастанию. В Москве четверо, и они выстроятся 19, 28, 41, 52. Если бы вторую колонку не написали, порядок москвичей между собой снова оказался бы неопределённым — а выглядел бы упорядоченным, потому что города-то стоят правильно.

У каждой колонки своё направление:

SELECT name, city, age
FROM customers
ORDER BY city ASC, age DESC

Города по алфавиту, а внутри города — от старшего к младшему. Слово DESC относится только к той колонке, после которой стоит, и на соседнюю не распространяется. Это вторая по частоте ошибка: ORDER BY city, age DESC — это не «всё по убыванию», а «города по возрастанию, возраст по убыванию».

Колонок в ORDER BY может быть сколько угодно, но больше трёх обычно означает, что вопрос сформулирован нечётко. Полезнее спросить, зачем нужен четвёртый уровень, чем написать его.

И ещё одно, что удивляет: сортировать можно по колонке, которой нет в ответе. SELECT name FROM customers ORDER BY age — законный запрос: имена выстроятся по возрасту, а самого возраста в ответе не будет. Иногда это удобно, иногда — источник вопроса «почему список в таком странном порядке», на который у читателя отчёта нет ответа.

Сортировать можно и по выражению: ORDER BY qty * price расставит строки заказов по сумме, даже если такой колонки в ответе нет. Это тот редкий случай, когда в ORDER BY разрешено больше, чем кажется: он выполняется после SELECT, а значит, видит и псевдонимы, и вычисленные колонки, и колонки таблицы, которых в списке вовсе нет.

Список колонок в ORDER BY удобно читать как составной ключ: «сначала по этому, при равенстве — по этому, при равенстве снова — по этому». Ровно так же устроены привычные бумажные списки: сотрудники по отделам, внутри отдела по фамилии, при одинаковой фамилии по имени. Ничего нового язык тут не придумал, и потому запись легко проверять вслух.

Отсюда и способ увидеть ошибку до того, как она попадёт в отчёт: прочитайте свой ORDER BY словами. «Города по алфавиту, внутри города возраст по убыванию» — если сказанное совпадает с просьбой заказчика, порядок верен. Если при чтении вслух получается «города по убыванию», значит, DESC стоит не там, где нужно.

LIMIT и OFFSET: топ и страницы

Последняя часть запроса — LIMIT: сколько строк вернуть.

SELECT title, price
FROM products
ORDER BY price DESC
LIMIT 3

Три самых дорогих товара: ноутбук, смартфон и планшет. Это и есть «топ-3», и записывается он всегда двумя частями сразу — сортировкой и ограничением.

Рядом с LIMIT живёт OFFSET — «сколько строк пропустить перед тем, как брать»:

SELECT title, price
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3

Строки с четвёртой по шестую: монитор, кресло и кофеварка. Так листают длинный список страницами: первая страница — OFFSET 0, вторая — OFFSET 3, третья — OFFSET 6.

Главное же правило этой главы такое: LIMIT без ORDER BY не значит ничего. «Первые три» — первые в каком порядке? Без сортировки база вернёт три строки на своё усмотрение, и завтра это могут быть другие три.

-- бессмысленный запрос: три какие-то строки
SELECT title FROM products LIMIT 3

-- осмысленный: три самых дорогих
SELECT title FROM products ORDER BY price DESC LIMIT 3

Единственный случай, когда LIMIT без сортировки уместен, — «покажи хоть что-нибудь, чтобы понять, как выглядят данные». Там порядок и правда не важен, и запрос честно говорит об этом отсутствием ORDER BY.

Про цену. LIMIT не делает запрос дешёвым. Он применяется последним — когда строки уже отобраны, свёрнуты и отсортированы, — поэтому «возьму первые десять, чтобы посмотреть побыстрее» на большой таблице экономит только пересылку, а не работу базы. Исключение одно: если сортировка идёт по колонке с индексом, база может взять нужные строки, не читая остальные.

Отдельно стоит назвать самый частый вид ограничения — одна строка. LIMIT 1 вместе с сортировкой отвечает на целый класс вопросов: самый свежий заказ, самый дорогой товар, последний вход в систему. Записывается это всегда одинаково — порядок по нужной колонке в нужном направлении и ограничение в одну строку.

И тут же ловушка, которую стоит знать до того, как в неё попасть: «одна строка» не значит «единственная». Если у нескольких строк одинаковое значение сортировки, LIMIT 1 вернёт одну из них — какую именно, не определено. Вопрос «какой заказ был последним, если их два в одну секунду» решается не запросом, а уточнением правила; запрос лишь честно покажет, что таких строк больше одной.

Где сортировка молча врёт

Сортировка кажется простой, и потому её ошибки живут в отчётах дольше прочих: список выглядит упорядоченным, а порядок в нём не тот. Четыре случая, ради которых написана эта глава.

Первый: число, сохранённое текстом. Текст сравнивается по алфавиту, и «100» оказывается меньше «99», потому что единица идёт раньше девятки. В отсортированном списке это видно сразу — числа выстраиваются как-то не так, — но только если посмотреть.

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

Третий: одинаковые значения. Если по колонке сортировки у двух строк одно и то же, их взаимный порядок не определён — и может меняться от запроса к запросу. Для отчёта это обычно неважно, а для постраничного вывода смертельно: одна и та же строка попадёт на две страницы, а другая не попадёт ни на одну.

Лечится это одинаково везде: последней колонкой сортировки ставят что-нибудь уникальное, чаще всего номер строки. ORDER BY price DESC, id — и порядок становится однозначным при любых совпадениях.

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

Общее правило урока короткое: «первые» и «последние» существуют только внутри заданного порядка. Вопрос «покажи последний заказ клиента» без указания, по чему считать последним — по дате, по номеру, по дате изменения, — не имеет ответа, и отвечать на него запросом нельзя. Сначала уточняют, потом пишут.

Проверить тип колонки, кстати, можно и не выходя из этого урока. Отсортируйте по ней и посмотрите на границы списка: у числовой колонки сверху окажется самое маленькое число, у текстовой — то, что начинается с самой ранней цифры. 1000 в начале списка, где есть 9, — верный признак, что колонка текстовая, как бы она ни называлась.