MySQL / 6. Сортировка результатов запроса

Использование ORDER BY для сортировки результатов запроса

Задача
Результаты запроса выводятся не в том порядке, в котором хотелось бы.

Решение
Добавьте в запрос инструкцию ORDER BY.

Обсуждение
Содержимое таблиц driver_log и mail, приведенных во введении, не упорядочено и неудобно для восприятия. Исключением являются столбцы id и t, в которых значения расположены по порядку, но это простое совпадение.

Обычно строки возвращаются в том же порядке, что и вставлялись, но только до тех пор, пока к таблице не будет применена операция обновления или удаления. Строки, добавленные последними, вполне могут быть возвращены в середине результирующего множества. Многие пользователи MySQL обращают внимание на такое нарушение порядка извлечения строк и задаются вопросом: «Как хранить строки в таблице так, чтобы они извлекались в определенном порядке?». Но этот вопрос некорректен. Хранением строк занимается сервер, и мы в это не вмешиваемся. (Кроме того, даже если бы вы могли задать порядок хранения, чем бы это вам помогло, если бы нужно было упорядочивать результат каждый раз по-разному?)Когда вы выбираете записи, сервер извлекает их из базы данных и возвращает в произвольном порядке. Порядок извлечения строк может меняться в зависимости от того, какой индекс сервер использует при выполнении запроса. Даже если ваши строки естественным образом выдаются в нужном порядке, никаких гарантий относительно порядка извлечения строк реляционная база данных не дает, до тех пор, пока вы явно не укажете ей, каким он должен быть. Чтобы расположить строки результата запроса в определенном порядке, отсортируйте их, добавив инструкцию ORDER BY в предложение SELECT. Если инструкция ORDER BY отсутствует, то порядок извлечения строк может измениться при изменении содержимого таблицы. Если же такая инструкция присутствует, MySQL всегда будет выводить строки в заданном порядке.

Инструкция ORDER BY имеет следующие характеристики:

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

• Любой столбец может быть упорядочен по возрастанию (по умолчанию) или по убыванию.

• На столбцы можно ссылаться по имени, по их позиции в списке вывода или с помощью псевдонимов.

В разделе представлено несколько базовых приемов сортировки. В следующих рецептах будут описаны более сложные случаи. Как ни парадоксально это звучит, можно применять ORDER BY и для «разупорядочивания» результирующего множества. Такая возможность используется для расположения строк в случайном порядке или (в сочетании с LIMIT) для случайной выборки строки из результирующего множества.

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

Запрос производит сортировку по одному столбцу – имени водителя:

mysql> SELECT * FROM driver_log ORDER BY name;

+-------+--------+---------------+------+
| rec_id | name  | trav_date    | miles |
+--------+-------+---------------+------+
| 1         | Ben     | 2001-11-30 | 152  |
| 5         | Ben     | 2001-11-29 | 131  |
| 9         | Ben     | 2001-12-02 | 79    |
| 3         | Henry | 2001-11-29 | 300  |
| 4         | Henry | 2001-11-27 | 96    |
| 6         | Henry | 2001-11-26 | 115  |
| 8         | Henry | 2001-12-01 | 197  |
| 10       | Henry | 2001-11-30 | 203  |
| 2         | Suzi    | 2001-11-29 | 391  |
| 7         | Suzi    | 2001-12-02 | 502  |
+--------+-------+--------------+-------+

По умолчанию строки упорядочиваются по возрастанию. Можно задать упорядочивание по возрастанию и явно, добавив ASC после названия сортируемого столбца:

SELECT * FROM driver_log ORDER BY name ASC;

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

mysql> SELECT * FROM driver_log ORDER BY name DESC;

+-------+-------+---------------+------+
| rec_id | name | trav_date    | miles |
+--------+-------+--------------+------+
| 2         | Suzi    | 2001-11-29 | 391 |
| 7         | Suzi    | 2001-12-02 | 502 |
| 3         | Henry | 2001-11-29 | 300 |
| 4         | Henry | 2001-11-27 | 96   |
| 6         | Henry | 2001-11-26 | 115 |
| 8         | Henry | 2001-12-01 | 197 |
| 10       | Henry | 2001-11-30 | 203 |
| 1         | Ben     | 2001-11-30 | 152 |
| 5         | Ben     | 2001-11-29 | 131 |
| 9         | Ben     | 2001-12-02 | 79   |
+-------+--------+---------------+-----+

Если вы внимательно посмотрите на вывод только что приведенных запросов, то заметите, что, хотя строки и упорядочены по именам, но если для одного имени есть несколько строк, они никак не упорядочены (например, для Henry и Ben значения trav_date не отсортированы по дате). Все потому, что MySQL выполняет только те сортировки, которые явно указаны:

• Порядок строк в целом не определен до тех пор, пока не добавлена инструкция ORDER BY.

• Аналогично для группы строк, имеющих одно и то же значение столбца сортировки, порядок значений в других столбцах также не определен до тех пор, пока вы не укажете их в инструкции ORDER BY.

Чтобы обеспечить более полное управление выводом, укажите сортировку по нескольким столбцам, перечислив их через запятую. Следующий запрос упорядочивает строки по возрастанию значений столбца name (имя), а для каждого имени – по trav_date (дата):

mysql> SELECT * FROM driver_log ORDER BY name, trav_date;

+--------+-------+------------+-------+
| rec_id | name  | trav_date    | miles |
+--------+-------+------------+-------+
| 5         | Ben     | 2001-11-29 | 131  |
| 1         | Ben     | 2001-11-30 | 152  |
| 9         | Ben     | 2001-12-02 | 79    |
| 6         | Henry | 2001-11-26 | 115  |
| 4         | Henry | 2001-11-27 | 96    |
| 3         | Henry | 2001-11-29 | 300  |
| 10       | Henry | 2001-11-30 | 203  |
| 8         | Henry | 2001-12-01 | 197  |
| 2         | Suzi    | 2001-11-29 | 391  |
| 7         | Suzi    | 2001-12-02 | 502  |
+--------+-------+--------------+-------+

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

mysql> SELECT * FROM driver_log ORDER BY name DESC, trav_date DESC;

+-------+-------+---------------+-------+
| rec_id | name | trav_date    | miles |
+-------+-------+---------------+-------+
| 7        | Suzi    | 2001-12-02 | 502   |
| 2        | Suzi    | 2001-11-29 | 391   |
| 8        | Henry | 2001-12-01 | 197   |
| 10      | Henry | 2001-11-30 | 203   |
| 3        | Henry | 2001-11-29 | 300   |
| 4        | Henry | 2001-11-27 | 96     |
| 6        | Henry | 2001-11-26 | 115   |
| 9        | Ben     | 2001-12-02 | 79     |
| 1        | Ben     | 2001-11-30 | 152   |
| 5        | Ben     | 2001-11-29 | 131   |
+-------+--------+--------------+-------+

При сортировке по нескольким столбцам вы можете задать для одних упорядочивание по возрастанию, а для других – по убыванию. Следующий запрос упорядочивает строки по убыванию значений столбца name, а затем для каждого имени – по возрастанию значений столбца trav_date:

mysql> SELECT * FROM driver_log ORDER BY name DESC, trav_date;

+--------+-------+------------+-------+
| rec_id | name | trav_date    | miles |
+-------+-------+---------------+-------+
| 2        | Suzi    | 2001-11-29 | 391   |
| 7        | Suzi    | 2001-12-02 | 502   |
| 6        | Henry | 2001-11-26 | 115   |
| 4        | Henry | 2001-11-27 | 96     |
| 3        | Henry | 2001-11-29 | 300   |
| 10      | Henry | 2001-11-30 | 203   |
| 8        | Henry | 2001-12-01 | 197   |
| 5        | Ben     | 2001-11-29 | 131   |
| 1        | Ben     | 2001-11-30 | 152   |
| 9        | Ben     | 2001-12-02 | 79     |
+-------+--------+--------------+-------+

Ссылки на столбцы сортировки
Инструкции ORDER BY уже рассмотренных запросов ссылаются на столбцы сортировки по имени. Можно также использовать для ссылок позиции столбцов в списке вывода или псевдонимы (alias). Нумерация позиций начинается с 1. Упорядочим результирующее множество по третьему столбцу miles:mysql> SELECT name, trav_date, miles FROM driver_log ORDER BY 3;

+-------+---------------+-------+
| name | trav_date     | miles |
+-------+---------------+-------+
| Ben     | 2001-12-02 | 79     |
| Henry | 2001-11-27 | 96     |
| Henry | 2001-11-26 | 115   |
| Ben     | 2001-11-29 | 131   |
| Ben     | 2001-11-30 | 152   |
| Henry | 2001-12-01 | 197   |
| Henry | 2001-11-30 | 203   |
| Henry | 2001-11-29 | 300   |
| Suzi    | 2001-11-29 | 391   |
| Suzi    | 2001-12-02 | 502   |
+-------+--------------+-------+

Если столбец вывода имеет псевдоним, вы можете указать его в инструкции ORDER BY:

mysql> SELECT name, trav_date, miles AS distance FROM driver_log -> ORDER BY distance;

+-------+---------------+----------+
| name | trav_date     | distance |
+-------+---------------+----------+
| Ben     | 2001-12-02 | 79          |
| Henry | 2001-11-27 | 96          |
| Henry | 2001-11-26 | 115        |
| Ben     | 2001-11-29 | 131        |
| Ben     | 2001-11-30 | 152        |
| Henry | 2001-12-01 | 197        |
| Henry | 2001-11-30 | 203        |
| Henry | 2001-11-29 | 300        |
| Suzi    | 2001-11-29 | 391        |
| Suzi    | 2001-12-02 | 502        |
+-------+--------------+----------+

Псевдонимы имеют преимущество перед столбцами, указанными номерами позиций в инструкции ORDER BY. Если вы выполняете сортировку, указывая позицию столбца в списке вывода, то при изменении этого списка вам может понадобиться изменить номера позиций столбцов в инструкции ORDER BY. Если вы используете псевдонимы столбцов, в этом нет необходимости. (К сожалению, некоторые процессоры баз данных не поддерживают псевдонимы столбцов в инструкции ORDER BY, так что эта функциональность непереносима.)
Как и столбцы, указанные по имени, столбцы, указанные номерами позиций или псевдонимами, можно упорядочить по убыванию и возрастанию:

mysql> SELECT name, trav_date, miles FROM driver_log ORDER BY 3 DESC;

+-------+---------------+-------+
| name | trav_date     | miles |
+-------+---------------+-------+
| Suzi    | 2001-12-02 | 502   |
| Suzi    | 2001-11-29 | 391   |
| Henry | 2001-11-29 | 300   |
| Henry | 2001-11-30 | 203   |
| Henry | 2001-12-01 | 197   |
| Ben     | 2001-11-30 | 152   |
| Ben     | 2001-11-29 | 131   |
| Henry | 2001-11-26 | 115   |
| Henry | 2001-11-27 | 96     |
| Ben     | 2001-12-02 | 79     |
+-------+---------------+-------+

Статьи по MySQL на эту тему:

Размещение некоторых значений в начале или конце упорядоченного списка
Сортировка IP-адресов в числовом порядке
Сортировка в порядке, определенном пользователем
Сортировка значений ENUM
Сортировка и значения NULL