Sorting, ORDER BY operator

When executing a SELECT query, the rows are returned by default in an undefined order. In fact, the order of rows in this case depends on the connection and scanning plan, as well as the order of data placement on the disk, so it cannot be relied upon. The ORDER BY construct is used to order the records.

General query structure with ORDER BY operator

MySQL
SELECT table_fields FROM table_name
WHERE ...
ORDER BY column_1 [ASC | DESC][, column_n [ASC | DESC]]

Where ASC and DESC are sorting directions:

• ASC - sorting in ascending order (by default)
• DESC - sorting in descending order

For example, Let's display the names of airlines in alphabetical order from the Company table:

MySQL
SELECT name FROM Company ORDER BY name;

Sorting in ascending and descending order for main types

Sorting by multiple columns

To sort results by two or more columns, they should be specified separated by commas.

MySQL
...ORDER BY column_1 [ASC | DESC], column_2 [ASC | DESC];

Data will be sorted by the first column, but in case there are multiple records with identical values in the first column, they will be sorted by the second column. The number of columns that can be sorted is unlimited.

The sorting rule applies only to the column that follows it.

ORDER BY column_1, column_2 DESC

is not the same as

ORDER BY column_1 DESC, column_2 DESC

Let's display flight information sorted by the departure city in ascending order and by the arrival city in descending order, from the Trip table:

MySQL
SELECT DISTINCT town_from, town_to FROM Trip
ORDER BY town_from, town_to DESC;

In this example, the entries are sorted by the town_from field first. Then, it performs reverse sorting by the town_to field for groups of rows that have the same value in the town_from column.

Demonstration of how sorting works