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

Бран · продюсер «Хроник Пограничья»

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

Последнее предложение Брана важнее, чем кажется. До сих пор ты отбирал строки и не думал, в каком порядке они придут. Сегодня учимся этим порядком управлять: сортировать, брать первые строки и резать результат на страницы.

Без сортировки порядка нет

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

База ведёт себя так же. Таблица в SQL это не список, а множество строк без собственного порядка. Если в запросе нет ORDER BY, PostgreSQL отдаёт строки так, как ему удобнее читать с диска, и это не обещание. После обновления данных порядок может поменяться.

ORDER BY: сортировка

ORDER BY ставится после WHERE и говорит, по какому столбцу упорядочить результат. По умолчанию сортировка идёт по возрастанию, это ASC. Самые дешёвые предложения магазина:

SELECT id, title, price_rub
FROM store_offers
ORDER BY price_rub
LIMIT 3;

 id | title         | price_rub
----+---------------+-----------
  1 | Горсть гемов  |     99.00
  6 | Набор новичка |    149.00
 18 | Смена имени   |    149.00

Для обратного порядка пишут DESC:

SELECT id, title, price_rub
FROM store_offers
ORDER BY price_rub DESC
LIMIT 3;

 id | title          | price_rub
----+----------------+-----------
  5 | Казна гемов    |   4990.00
 16 | Ездовой грифон |   2490.00
  4 | Гора гемов     |   1990.00

LIMIT 3 здесь отрезает первые три строки уже отсортированного результата. Порядок частей запроса фиксированный: SELECT, FROM, WHERE, ORDER BY, LIMIT. И выполняются они логически так же: сначала отбор, потом сортировка, потом обрезка.

Ничьи и тай-брейк

Теперь топ героев. Первая попытка:

SELECT id, name, level
FROM characters
ORDER BY level DESC
LIMIT 3;

 id  | name       | level
-----+------------+-------
 142 | iron_mivel |    60
  97 | Mormiul    |    60
 168 | SunShadow  |    60

Выглядит нормально, но героев 60-го уровня в базе 63. Какие три из них показать, база решила сама, и решение ничем не обосновано. Это и есть перетасовка, которой боится Бран.

Нужен второй ключ сортировки, тай-брейк: чем различать героев с одинаковым уровнем. Логично взять опыт, а на случай равного опыта ещё и id, который у каждого свой.

SELECT id, name, class, level, xp
FROM characters
ORDER BY level DESC, xp DESC, id
LIMIT 10;

  id  | name           | class   | level | xp
------+----------------+---------+-------+---------
  300 | Liulmi         | mage    |    60 | 1249805
 3753 | BearHawk674    | priest  |    60 | 1244629
  859 | storm_riash    | rogue   |    60 | 1241176
 3987 | ShadowFrost854 | ranger  |    60 | 1239615
 1997 | Velsador39     | rogue   |    60 | 1233328
 3383 | Torvel37       | mage    |    60 | 1231235
 2770 | farm239x       | ranger  |    60 | 1226835
 3585 | HawkFrost597   | mage    |    60 | 1225354
 1546 | FROST71        | rogue   |    60 | 1215875
 2702 | farm619x       | ranger  |    60 | 1215181

Сортировка идёт по ключам слева направо: сначала по уровню, внутри одного уровня по опыту, при равном опыте по id. И DESC пишется у каждого ключа отдельно: в ORDER BY level, xp DESC уровень отсортируется по возрастанию.

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