5 примеров сортировки с ORDER BY в SQLite

Строки в таблице SQLite не имеют фиксированного порядка, что означает отсутствие гарантированной последовательности без явной сортировки

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

SELECT select_list
FROM table
ORDER BY column_1 ASC, column_2 DESC;

Предложение ORDER BY следует после FROM и используется для сортировки результатов по одному или нескольким столбцам

В предложении ORDER BY указывается имя столбца для сортировки, после чего добавляется ключевое слово ASC (по возрастанию) или DESC (по убыванию)

Если не указано ASC или DESC, SQLite по умолчанию использует ASC, то есть сортирует по возрастанию

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

SQLite также позволяет сортировать результирующий набор по столбцам, которые не входят в список выборки предложения SELECT

Содержание

Базовые примеры сортировки
Сортировка по нескольким столбцам
Сортировка по позиции столбца
Сортировка значений NULL
Частые ошибки при использовании ORDER BY в SQLite
Часто задаваемые вопросы

Базовые примеры сортировки

Для демонстрации возьмём таблицу tracks из учебной базы данных

Предположим, вы хотите получить данные из столбцов Name, Milliseconds и AlbumId:

SELECT Name, Milliseconds, AlbumId
FROM tracks;

Оператор SELECT без предложения ORDER BY возвращает строки в неопределённом порядке

Чтобы отсортировать результирующий набор по столбцу AlbumId в порядке возрастания, используйте следующий оператор:

SELECT name, milliseconds, albumid
FROM tracks
ORDER BY albumid ASC;

Теперь результирующий набор отсортирован по столбцу AlbumId по возрастанию. Поскольку SQLite использует ASC по умолчанию, ключевое слово в данном случае можно опустить:

SELECT name, milliseconds, albumid
FROM tracks
ORDER BY albumid;

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

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

Допустим, требуется дополнительно отсортировать уже упорядоченный по AlbumId результат по столбцу Milliseconds в порядке убывания. Для этого добавьте второй столбец в ORDER BY

SELECT name, milliseconds, albumid
FROM tracks
ORDER BY albumid ASC, milliseconds DESC;

SQLite сначала сортирует строки по столбцу AlbumId в порядке возрастания, затем сортирует полученный результирующий набор по столбцу Milliseconds в порядке убывания

Изменение порядка треков с AlbumId = 1 произошло именно потому, что внутри каждого альбома они теперь упорядочены от самого длинного к самому короткому

Сортировка по позиции столбца

Вместо указания названий столбцов в ORDER BY можно использовать их числовые позиции — порядковые номера в списке SELECT

Например, следующий оператор сортирует треки по столбцам AlbumId (3-й столбец) и Milliseconds (2-й столбец) в порядке возрастания:

SELECT name, milliseconds, albumid
FROM tracks
ORDER BY 3, 2;

Числа 3 и 2 соответствуют столбцам AlbumId и Milliseconds в списке SELECT. Этот способ удобен для быстрого написания запросов, однако в рабочий код / код в продуктивной средее лучше явно указывать имена столбцов для ясности

Сортировка значений NULL

В мире баз данных NULL — особое понятие. Оно означает, что информация отсутствует или данные неприменимы

Предположим, вы хотите хранить дату рождения исполнителя в таблице. В момент сохранения записи информация о дате рождения вам недоступна. Можно использовать специальное значение, например 01.01.1900 или пустую строку '', однако эти значения не дают чёткого понимания того, что дата рождения именно неизвестна

NULL был придуман именно для решения этой проблемы: вместо условного значения-заглушки применяется NULL

NULL особенен тем, что его нельзя сравнить с другим значением. Если два фрагмента информации неизвестны, их нельзя сравнить между собой. NULL нельзя сравнить даже с самим собой: NULL = NULL всегда возвращает false

При сортировке SQLite считает NULL меньше любого другого значения. Это означает, что значения NULL будут появляться в начале результирующего набора при использовании ASC или в конце при использовании DESC

В SQLite 3.30.0 в предложение ORDER BY были добавлены параметры NULLS FIRST и NULLS LAST. Параметр NULLS FIRST явно указывает, что значения NULL должны располагаться в начале результирующего набора, а NULLS LAST помещает их в конец

В следующем примере предложение ORDER BY используется для сортировки треков по композиторам:

SELECT TrackId, Name, Composer
FROM tracks
ORDER BY Composer;

Сначала в начале результирующего набора появляются значения NULL, поскольку SQLite считает их наименьшими. При прокрутке вниз вы увидите остальные значения

Чтобы разместить NULL после остальных значений, используйте параметр NULLS LAST:

SELECT TrackId, Name, Composer
FROM tracks
ORDER BY Composer NULLS LAST;

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

Частые ошибки при использовании ORDER BY в SQLite

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

Ожидание стабильного порядка без ORDER BY. Без явного предложения ORDER BY порядок строк не гарантирован — даже если в предыдущих запросах строки возвращались в одном и том же порядке. Полагаться на это поведение нельзя

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

Неожиданное поведение NULL. По умолчанию NULL оказывается в начале при ASC-сортировке. Если это нежелательно, явно указывайте NULLS LAST. Параметр доступен начиная с SQLite 3.30.0 — убедитесь, что используемая версия его поддерживает

Сортировка строковых чисел. Если числа хранятся в столбце с текстовым типом, SQLite будет сортировать их лексикографически: '10' окажется перед '9'. В таких случаях используйте явное приведение типа:

Используйте предложение ORDER BY в SQLite для сортировки строк по одному

или нескольким столбцам в порядке возрастания и убывания

Используйте ASC для сортировки строк по возрастанию

Используйте DESC для сортировки строк по убыванию

Используйте параметр NULLS FIRST для размещения значений NULL перед

остальными ненулевыми значениями

Используйте параметр NULLS LAST для размещения значений NULL после

остальных ненулевых значений

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

Что происходит, если не указать ORDER BY в запросе SELECT? SQLite возвращает строки в неопределённом порядке. Порядок может меняться от запроса к запросу и не должен использоваться как предсказуемый

Можно ли сортировать по столбцу, которого нет в SELECT? Да. SQLite позволяет указывать в ORDER BY столбцы, не входящие в список выборки предложения SELECT

Как работает сортировка по нескольким столбцам? SQLite обрабатывает столбцы в ORDER BY слева направо: сначала сортирует по первому столбцу, затем внутри одинаковых значений первого столбца — по второму, и так далее

Где окажутся значения NULL при сортировке по возрастанию? По умолчанию SQLite считает NULL меньше любого другого значения, поэтому при ASC-сортировке NULL оказывается в начале результирующего набора. Чтобы изменить это поведение, используйте NULLS LAST

С какой версии SQLite доступны параметры NULLS FIRST и NULLS LAST? Параметры NULLS FIRST и NULLS LAST были добавлены в SQLite начиная с версии 3.30.0