3.3.4.4 Сортировка строк

Вы уже, наверное, заметили, что результаты, выдававшиеся запросами из предыдущих примеров, выводились без какой-либо сортировки. Но ведь часто разобраться в результатах легче, если они отсортированы. Для этого используется выражение ORDER BY.

Так выглядят даты рождения животных в отсортированном виде:

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

Над столбцами с символьными значениями операция сортировки - как и все другие операции сравнения - обычно проводится без учета регистра символов. Это значит, что порядок расположения столбцов, совпадающих во всем, кроме регистра символов, относительно друг друга будет не определен. Провести сортировку с учетом регистра символов можно при помощи команды BINARY: ORDER BY BINARY(поле).

Для сортировки в обратном порядке к имени столбца следует добавить ключевое слово DESC (по убыванию):

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

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

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

Обратите внимание: действие ключевого слова DESC распространяется только на тот столбец, имя которого располагается в запросе прямо перед этим словом (в данном случае - birth); значения поля species по-прежнему отсортированы в возрастающем порядке.

User Comments

i want to make new mysql ?

mysql> CREATE DATABASE menagerie;

shell> mysql -h host -u ali -p menagerie
Enter password: 224227

mysql> USE menagerie
Database changed

i want to copy mysql ?

For an example of sorting based on a dynamically
generated column, see the example of sorting
based on age in section 3.3.4.5 "Date
Calculations".

For Windows users: the directory where you should
save 'pet.txt' is the 'tmpdir' as shown under
the 'variables'tab of the server WinMySQLAdmin
(probably c:\windows\TEMP).

Why does it order the rows improperly?

select number from numbers order by numbers;

number
--------
100000000
12800000

Note: 100000000 IS biggers then 12800000 BUT
mysql don't list it last.

A better way IMO than the DISTINCT clause would
be:

SELECT owner, count(*) FROM pet GROUP BY owner;

you don't have to make pet.txt in a specific
directory. you could use the absolute location
of the file. i.e. "c:/files/pet.txt". notice you
must use backslashes.

Those are "forward" slashes. You
mean "c:\files\pet.txt"

If you are trying to sort a field of numbers and
you find it coming out like this:

1
10
11
12
2
3
4

the reason is that the field type is not of the
INT variety, therefore it is ordering the
information as though it were a string. A quick
fix for this (though it is probably best to
change the field type to INT) is to force MySQL
to treat the string as a number by putting 0+
infront of the field name in the ORDER BY clause.

ie: SELECT month FROM searchlog ORDER BY 0+month;

I hope this helps!

P.S. That is a zero not a capital 'o'.

I found that mysql can not optimize Order by
very well.I if i use a "order by last asc",then
mysql will use the index to retrieve data,if i
want to use "order by last DESC",then mysql
always use filesort,why?

As a corollary to Peter's comment above -- I had
the same situation except field values like 1 1A
1B 1C 1D -- sorting came out 1 1D 1C 1B 1A no
matter what I did (binary, field+0, etc.). So I
created a second field of integer type and
inserted the same value into it (update table set
f2=f1); now when I do my selects as "order by
f2,f1" it works just fine.

I tried sorting using 3 columns i.e. 'ORDER BY a, b, c'. However mysql (version 3.22.23b) used columns b & c to sort and then a; I guess that the reason was that there was a primary key defined over columns b & c and thus the sorting on column a was optimised away?

MySQL seems not able to sort with respect for
german umlauts and such. That means that "Д,Ж,Э"
will be put to the very front, even before the dot.

I would really like to be able to sort columns using
regular expressions but have yet not had any luck
with this. Have any of you have any experience with
this and where could I get more information on how
MySQL sorts with regard to regular expressions.

Add your own comment.