MySQL? Reference manual (sort line)

Sorting row

You may have noticed in the previous example that the result rows are not displayed in a specific order. When the rows are sorted in a meaningful way, it is usually easier to check the query output. To sort the results, use the ORDER BY clause.

This is the birthday of the animal, sorted by date:

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 |
+----------+------------+

In the character type column, sorting is usually performed in a case insensitive manner like all other comparison operations, which means that for the same column, the order is undefined, except for the case difference. You can use BINARY to force case sensitive sorting of columns, as shown below: order by BINARY col ű u name.

The default sort order is ascending, first minimum. To sort in reverse (descending) order, add the DESC keyword to the name of the column to sort:

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 |
+----------+------------+

You can sort on multiple columns, and you can sort in different directions on different columns. For example, to sort by animal type in ascending order, then by birth date in animal type in descending order (the youngest animal first), use the following query:

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 |
+----------+---------+------------+

The DESC keyword only applies to the immediately preceding column name (birth), which does not affect the sort order of the categories column.

Previous: retrieving information from a table

Tags: MySQL

Posted on Sat, 02 Nov 2019 05:15:33 -0700 by todd2006