ORDER BY en LIMIT: volgorde en toplijsten

Ordenen en toplijsten maken

Tot nu toe antwoordde het archief in een willekeurige volgorde: de rijen kwamen terug zoals dat voor een machine handig was, niet voor een mens. Vandaag breng je voor het eerst orde in de uitvoer, en «Kotomarket» laat meer zien dan losse rijen: wat in deze winkel als duur gold, en wat goed verkocht.

ORDER BY kolom bepaalt de volgorde van het resultaat: oplopend (ASC, standaard) of aflopend (DESC). Je kunt op meerdere kolommen sorteren, gescheiden door komma's. Zo krijg je een stabiel rapport in plaats van een willekeurige rijvolgorde.

LIMIT n houdt alleen de eerste n rijen over. De combinatie ORDER BY ... DESC LIMIT n is de klassieke toplijst: de duurste artikelen, de grootste bestellingen, de meest recente gebeurtenissen. Zonder sortering betekenen de «eerste» rijen niets.

QUERY: «De eerste rijen zonder ORDER BY» is als «het beste pronkstuk op goed geluk». Het archief belooft geen volgorde totdat je erom vraagt.

Без ORDER BY порядка нет

Очень важное правило:

SQL не гарантирует порядок строк без ORDER BY.

Например:

SELECT id, name, price
FROM products;

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

Иногда кажется, что база возвращает строки:

• в порядке вставки;
• по id;
• как они лежат в таблице;
• как они отображаются в интерфейсе.

Но на это нельзя опираться.

База может выбрать другой план выполнения, использовать другой индекс, иначе прочитать данные после обновления статистики или после изменения таблицы. И порядок результата поменяется, хотя сам SQL-запрос останется тем же.

Если порядок важен, он должен быть явно написан:

SELECT id, name, price
FROM products
ORDER BY id;

или:

SELECT id, name, price
FROM products
ORDER BY price DESC;

Правило простое:

Нет ORDER BY — нет обещанного порядка.

Это особенно важно перед LIMIT. Потому что LIMIT берёт «первые строки», а без сортировки непонятно, какие строки база посчитает первыми.

ASC и DESC

У ORDER BY есть два направления сортировки.

ASC — по возрастанию:

SELECT name, price
FROM products
ORDER BY price ASC;

Для цены это значит:

сначала дешёвые, потом дорогие.

DESC — по убыванию:

SELECT name, price
FROM products
ORDER BY price DESC;

Для цены это значит:

сначала дорогие, потом дешёвые.

Если направление не указано, используется ASC.

То есть эти два запроса равнозначны:

SELECT name, price
FROM products
ORDER BY price;

SELECT name, price
FROM products
ORDER BY price ASC;

Для новичка полезно читать запрос вслух:

ORDER BY price ASC

— отсортируй по цене от меньшей к большей.

ORDER BY price DESC

— отсортируй по цене от большей к меньшей.

LIMIT: взять только первые строки

LIMIT ограничивает количество строк в результате.

Например:

SELECT name, price
FROM products
LIMIT 3;

Этот запрос вернёт только три строки.

Но сам по себе LIMIT не говорит, какие именно строки нужны. Он просто отрезает первые строки из того результата, который получился.

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

Три самых дешёвых товара:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

Три самых дорогих товара:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Самые свежие заказы:

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Логика всегда одинаковая:

1. ORDER BY выстраивает строки в нужном порядке.
2. LIMIT берёт верхушку этого порядка.

ORDER BY price DESC
LIMIT 5

читается так:

поставь самые дорогие товары наверх и возьми первые пять.

Почему ORDER BY стоит почти в конце запроса

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

Например:

SELECT name, price
FROM products
WHERE category = 'Книги'
ORDER BY price DESC
LIMIT 5;

Такой запрос можно читать по шагам:

1. FROM products — возьми таблицу товаров.
2. WHERE category = 'Книги' — оставь только книги.
3. SELECT name, price — выбери нужные столбцы.
4. ORDER BY price DESC — отсортируй результат по цене.
5. LIMIT 5 — оставь первые пять строк.

То есть сортировка применяется не ко всей таблице вообще, а к результату после фильтрации.

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

Это помогает правильно читать запросы:

WHERE

отвечает за то, какие строки участвуют.

ORDER BY

отвечает за то, в каком порядке они будут показаны.

LIMIT

отвечает за то, сколько строк останется сверху.

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

Иногда одного столбца недостаточно.

Например, нужно отсортировать товары:

1. сначала по категории;
2. внутри каждой категории — по цене от дорогих к дешёвым.

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

SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

Этот запрос читается так:

сначала отсортируй по категории по возрастанию, а если категория одинаковая — внутри неё отсортируй по цене по убыванию.

Пример результата:

Первый ключ сортировки главный:

category ASC

Он собирает строки по категориям.

Второй ключ применяется только там, где первый одинаковый:

price DESC

Он упорядочивает товары внутри одной категории.

Можно задавать своё направление для каждого столбца:

ORDER BY category ASC, price DESC, name ASC

Это значит:

1. категория по возрастанию;
2. внутри категории цена по убыванию;
3. если цена одинаковая, название по возрастанию.

Сортировка по выражению

Сортировать можно не только по готовому столбцу, но и по вычислению.

Например, в таблице products есть:

• price — цена товара;
• stock — остаток на складе.

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

price * stock

Запрос:

SELECT name, price, stock
FROM products
ORDER BY price * stock DESC;

Он сортирует товары не просто по цене и не просто по остатку, а по произведению цены на количество.

То есть товар за 1000 с остатком 100 может оказаться выше, чем товар за 5000 с остатком 2.

Потому что:

1000 * 100 = 100000
5000 * 2   = 10000

ORDER BY умеет работать с такими выражениями:

ORDER BY price * stock DESC

Это удобно, когда порядок зависит от расчёта, а не от одного столбца.

Сортировка по алиасу

Если выражение длинное, его неудобно повторять в ORDER BY.

Можно дать выражению имя через алиас:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;

Здесь:

price * stock AS stock_value

создаёт в результате вычисляемый столбец stock_value.

А потом:

ORDER BY stock_value DESC

сортирует по этому вычисленному значению.

Это работает потому, что ORDER BY логически применяется после формирования списка SELECT. К моменту сортировки алиас stock_value уже существует.

Но важно не перепутать с WHERE.

Так нельзя:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
WHERE stock_value > 10000;

В WHERE алиас из SELECT ещё недоступен.

Правильно либо повторить выражение:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
WHERE price * stock > 10000
ORDER BY stock_value DESC;

Либо использовать подзапрос — но это уже тема следующих модулей.

Главная мысль этого урока:

В ORDER BY алиас из SELECT использовать можно. В WHERE — нельзя.

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

NULL означает отсутствие значения. При сортировке нужно решить, где такие строки окажутся:

• в начале;
• в конце.

В PostgreSQL по умолчанию NULL при сортировке считается больше любого обычного значения.

Поэтому при сортировке по возрастанию:

ORDER BY city ASC

строки с NULL окажутся в конце.

А при сортировке по убыванию:

ORDER BY city DESC

строки с NULL окажутся в начале.

Чтобы не зависеть от умолчаний, можно написать явно:

ORDER BY city ASC NULLS FIRST

Это значит:

сортируй города по возрастанию, но строки без города поставь в начало.

Или:

ORDER BY city ASC NULLS LAST

Это значит:

сортируй города по возрастанию, а строки без города поставь в конец.

Можно использовать и с DESC:

ORDER BY price DESC NULLS LAST

Это удобно для топов.

Например, если у некоторых товаров цена неизвестна, то запрос:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

в PostgreSQL может поднять товары с NULL наверх, потому что при DESC NULL идут первыми.

Чтобы получить именно самые дорогие товары с известной ценой, лучше написать:

SELECT name, price
FROM products
ORDER BY price DESC NULLS LAST
LIMIT 5;

Так товары с неизвестной ценой не помешают топу.

Важно для переносимости: в разных СУБД порядок NULL по умолчанию может отличаться. В PostgreSQL можно явно использовать NULLS FIRST и NULLS LAST. В MySQL такой синтаксис не используется, и поведение по умолчанию другое: NULL обычно считается меньше обычных значений.

Ничьи: когда значения одинаковые

Представь, что мы выбираем три самых дорогих товара:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Если цены у всех товаров разные, порядок понятен.

Но что если несколько товаров стоят одинаково?

Например:

У первых трёх товаров одинаковая цена.

Условие:

ORDER BY price DESC

гарантирует только одно:

товары за 3000 будут выше товаров за 1000.

Но оно не гарантирует, в каком порядке между собой будут товары за 3000.

Для глаза это может казаться мелочью. Но для отчётов, тестов и постраничной выдачи это важно.

Если несколько строк имеют одинаковое значение в столбце сортировки, их взаимный порядок не определён. Он может измениться после обновления данных, смены плана выполнения или даже между похожими запусками.

Чтобы порядок стал полностью предсказуемым, добавляют второй ключ сортировки — обычно уникальный id:

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3;

Теперь сортировка читается так:

1. сначала по цене от дорогих к дешёвым;
2. если цена одинаковая — по id от меньшего к большему.

Такой порядок уже стабилен.

Стабильный порядок для LIMIT и страниц

Недетерминированный порядок особенно опасен вместе с LIMIT.

Например:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 10;

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

Сегодня товар попадёт в топ-10.
Завтра при тех же ценах может не попасть.

Это не баг базы. Просто ты не полностью описал порядок.

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

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 10;

Особенно это важно для пагинации.

Например, первая страница:

ORDER BY price DESC
LIMIT 10

и вторая страница через смещение:

ORDER BY price DESC
LIMIT 10 OFFSET 10

Если у строк одинаковая цена и нет дополнительной сортировки по id, одна и та же строка может «прыгать» между страницами.

Надёжнее:

ORDER BY price DESC, id ASC
LIMIT 10 OFFSET 10

Главное правило:

Если используешь LIMIT для отчёта, топа или страницы, сортировка должна полностью определять порядок строк.

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

Часто в отчёте не хочется показывать технические столбцы, но по ним нужно отсортировать результат.

Например, нужно показать названия товаров, но отсортировать их по цене:

SELECT name
FROM products
ORDER BY price DESC;

Так можно.

price не обязан быть в списке SELECT, чтобы использоваться в ORDER BY.

Запрос вернёт только name, но порядок строк будет определён ценой.

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

Например:

SELECT name
FROM products
ORDER BY created_at DESC
LIMIT 10;

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

Типовые связки ORDER BY и LIMIT

Связка ORDER BY + LIMIT появляется в SQL постоянно.

Самые дорогие товары:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

Самые дешёвые товары:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 5;

Самые свежие заказы:

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Первые пользователи по алфавиту:

SELECT id, name
FROM users
ORDER BY name ASC
LIMIT 20;

Товары с самым большим остатком:

SELECT name, stock
FROM products
ORDER BY stock DESC
LIMIT 10;

Товары с максимальной складской стоимостью:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 10;

Во всех случаях схема одна:

ORDER BY что_считаем_важным
LIMIT сколько_строк_нужно

КВЕРИ: Сначала скажи архиву, что считать важным. Только потом проси верхушку.