Как работают ORDER BY, LIMIT и OFFSET
На этой странице
ORDER BY задаёт порядок результата (ASC по умолчанию, DESC — по убыванию, несколько ключей применяются последовательно, NULLS FIRST/LAST управляет местом NULL). Без ORDER BY порядок не гарантирован. LIMIT n возвращает не больше n строк, OFFSET k пропускает первые k. Для стабильного порядка сортировка должна быть однозначной — добавляют уникальный ключ (id) последним. Глубокий OFFSET медленный (база всё равно читает и отбрасывает пропущенные строки) и неустойчив к вставкам — для лент используют keyset-пагинацию.
Вопрос
Как работают ORDER BY, LIMIT и OFFSET и в чём их подводные камни?
Сортировка
SELECT id, title, price
FROM products
ORDER BY category_id ASC, price DESC, id; -- сначала категория, внутри — цена, затем idORDER BY rating DESC NULLS LAST -- товары без рейтинга — в конце
ORDER BY 2 -- по номеру столбца (плохо читается)
ORDER BY lower(title) -- по выражению
ORDER BY CASE status WHEN 'urgent' THEN 0 ELSE 1 END, created_atВ PostgreSQL NULL по умолчанию считаются больше любых значений: в конце при ASC, в начале при DESC.
LIMIT и OFFSET
SELECT id, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40; -- третья страница по 20Стандартный синтаксис: OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY. В SQL Server — TOP, в старых Oracle — ROWNUM.
Стабильность порядка
-- ✗ у многих постов одинаковый created_at — между страницами строки «прыгают»
ORDER BY created_at DESC
-- ✓ уникальный tie-breaker
ORDER BY created_at DESC, id DESCЕсли ключ сортировки не уникален, СУБД может вернуть равные строки в разном порядке в разных запросах, и при пагинации часть записей повторится, а часть пропадёт.
Проблемы OFFSET
| Проблема | Причина |
|---|---|
| Медленно на дальних страницах | Чтобы вернуть 20 строк с OFFSET 100000, нужно прочитать 100 020 |
| Дубли и пропуски | Новая запись сверху сдвигает все страницы |
| Нагрузка | Боты, листающие до 5000-й страницы, нагружают БД |
Решение — keyset (cursor) pagination: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20 — см. урок о пагинации.
Индекс и ORDER BY ... LIMIT
CREATE INDEX posts_created_idx ON posts (created_at DESC, id DESC);С подходящим индексом база читает первые 20 записей индекса и останавливается — без сортировки всей таблицы. Подробнее — в уроке индекс и ORDER BY.
Частые ошибки
- Полагаться на порядок без
ORDER BY(например, «строки возвращаются по id»). - Неуникальный ключ сортировки при пагинации.
OFFSETдля бесконечной ленты и экспорта больших таблиц.LIMITбезORDER BY— «случайные» строки.
Что важно помнить
- Порядок — только явный и однозначный.
LIMITбезORDER BYбессмыслен для пагинации.OFFSETхорош для небольших списков и номеров страниц, keyset — для лент и больших объёмов.
Мини-шпаргалка
ORDER BY a ASC, b DESC, id.NULLS FIRST/LAST.LIMIT n OFFSET k.- Уникальный tie-breaker в конце сортировки.
- Глубокие страницы — keyset.