MySQL ORDER BY

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A table has no order. Rows come back in whatever order the server finds convenient, and that can change between one run and the next. ORDER BY is the only thing that decides the order of a result. Open the client with mysql -u root -p sakila before you start.

How ORDER BY works

SELECT COLUMN_LIST
FROM TABLE_NAME
ORDER BY SORT_COLUMN DIRECTION;

Replace SORT_COLUMN with the column to sort on. DIRECTION is either ASC for smallest first or DESC for largest first. It is optional, and leaving it out means ASC.

Examples

1) Sort by one column

SELECT title FROM film ORDER BY title;

+-----------------------------+
| title                       |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
| AFFAIR PREJUDICE            |
| AFRICAN EGG                 |
...
+-----------------------------+
1000 rows in set (0.01 sec)

2) Sort the other way

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC;

+-----------------------------+-------------+
| title                       | rental_rate |
+-----------------------------+-------------+
| ACE GOLDFINGER              |        4.99 |
| AIRPLANE SIERRA             |        4.99 |
| AIRPORT POLLOCK             |        4.99 |
| ALADDIN CALENDAR            |        4.99 |
| ALI FOREVER                 |        4.99 |
...
+-----------------------------+-------------+
1000 rows in set (0.00 sec)

Sakila prices every film at 1 of only 3 rates, and 336 films share the top rate of 4.99. The order among those 336 tied rows is not decided by this query, so it can differ from the order you see here.

3) Break the tie with a second column

List sort columns in order of priority, separated by commas. Each one takes its own direction.

SELECT title, rental_rate, length
FROM film
ORDER BY rental_rate DESC, length;

+-----------------------------+-------------+--------+
| title                       | rental_rate | length |
+-----------------------------+-------------+--------+
| IRON MOON                   |        4.99 |     46 |
| HANOVER GALAXY              |        4.99 |     47 |
| ACE GOLDFINGER              |        4.99 |     48 |
| MIDSUMMER GROUNDHOG         |        4.99 |     48 |
| PELICAN COMFORTS            |        4.99 |     48 |
...
+-----------------------------+-------------+--------+
1000 rows in set (0.00 sec)

rental_rate still leads, descending. Within each rate the shortest film comes first. Films of equal length are still tied, so a fully decided order needs enough columns to make every row unique.

4) Sort by an alias

ORDER BY can name a column alias from the same query.

SELECT title AS film_title FROM film ORDER BY film_title;

+-----------------------------+
| film_title                  |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
...
+-----------------------------+
1000 rows in set (0.00 sec)

Quote an alias with backticks, not single quotes

An alias holding a space has to be quoted, and here the 2 quoting styles stop agreeing. Single quotes make a string rather than a name, and sorting by a fixed string sorts nothing.

SELECT title, rental_rate AS 'Rate' FROM film ORDER BY 'Rate';

+-----------------------------+------+
| title                       | Rate |
+-----------------------------+------+
| ACADEMY DINOSAUR            | 0.99 |
| ACE GOLDFINGER              | 4.99 |
| ADAPTATION HOLES            | 2.99 |
| AFFAIR PREJUDICE            | 2.99 |
| AFRICAN EGG                 | 2.99 |
...
+-----------------------------+------+
1000 rows in set (0.00 sec)

The rates run 0.99, 4.99, 2.99, so nothing was sorted. The server read 'Rate' as the 4 letter word and sorted every row by the same value. No error, no warning, no sorting. Backticks name the alias, and the sort happens:

SELECT title, rental_rate AS `Rate` FROM film ORDER BY `Rate`;

+-----------------------------+------+
| title                       | Rate |
+-----------------------------+------+
| ACADEMY DINOSAUR            | 0.99 |
| ALAMO VIDEOTAPE             | 0.99 |
| ALASKA PHANTOM              | 0.99 |
| ALICE FANTASIA              | 0.99 |
| ALONE TRIP                  | 0.99 |
...
+-----------------------------+------+
1000 rows in set (0.00 sec)

Every row shown is at 0.99, so their order among themselves is not decided here either.

Where NULL lands

A column can hold NULL, which records that no value is there at all. It is not a zero and not an empty string. MySQL sorts NULL below every real value, so ascending order puts the NULLs first. Of the 603 rows in address, 4 have no address2.

SELECT address_id, address2 FROM address ORDER BY address2;

+------------+----------+
| address_id | address2 |
+------------+----------+
|          1 | NULL     |
|          2 | NULL     |
|          3 | NULL     |
|          4 | NULL     |
|        303 |          |
|        605 |          |
...
+------------+----------+
603 rows in set (0.00 sec)

The 4 NULL rows come first. The rows after them hold an empty string, which is a real value, so it sorts above NULL and below any text. Those 4 NULL rows are tied with each other, so the order among them is not decided by this query. DESC reverses it and the NULLs go to the bottom. This is the end of the same result sorted the other way:

SELECT address_id, address2 FROM address ORDER BY address2 DESC;

+------------+----------+
| address_id | address2 |
+------------+----------+
...
|        532 |          |
|          1 | NULL     |
|          2 | NULL     |
|          3 | NULL     |
|          4 | NULL     |
+------------+----------+
603 rows in set (0.00 sec)

Summary

Use ORDER BY to decide the order of a result, because nothing else does.
Write DESC for largest first; ASC is the default.
List several sort columns, in priority order, to break ties.
Quote an alias with backticks when sorting by it, because single quotes make a string.
Expect NULL first when sorting ascending, and last when sorting descending.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How ORDER BY works

Examples

1) Sort by one column

2) Sort the other way

3) Break the tie with a second column

4) Sort by an alias

Quote an alias with backticks, not single quotes

Where NULL lands

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How ORDER BY works

​Examples

​1) Sort by one column

​2) Sort the other way

​3) Break the tie with a second column

​4) Sort by an alias

​Quote an alias with backticks, not single quotes

​Where NULL lands

​Summary

​See also

How ORDER BY works

Examples

1) Sort by one column

2) Sort the other way

3) Break the tie with a second column

4) Sort by an alias

Quote an alias with backticks, not single quotes

Where NULL lands

Summary

See also