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

SELECT table_fields FROM table_name
[WHERE condition]
ORDER BY column [ASC | DESC] [, ...];

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

MySQL 8.4
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.

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.

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