Data sorting, ORDER BY operator

When executing a SELECT query, the default rows are returned in an undefined order. The actual row order in this case depends on the connection and scanning plan, as well as on the order of the data on the disk, so you cannot rely on it. The ORDER BY operator is used to organize records.

General query structure with the ORDER BY operator

MySQL
SELECT table_fields FROM list_of_tables
ORDER BY table_field_1 [ASC | DESC][, table_field_n [ASC | DESC]]

In the described query structure, optional parameters are indicated in square brackets.

  • DESC — descending sort
  • ASC (default) — ascending sort

Multi column sort

To sort the results by two or more columns, they must be separated by commas.

MySQL
SELECT table_fields FROM list_of_tables
ORDER BY table_field_1 [ASC|DESC], 
table_field_2 [ASC|DESC];

Data will be sorted by the first column, but if there are several records with matching values in the first column, then they are sorted by the second column. The number of columns by which you can sort is not limited.

A sort rule applies only to the column that follows it.

ORDER BY column_1, column_2 DESC
not the same as
ORDER BY column_1 DESC, column_2 DESC

Examples

List the names of airlines in alphabetical order from the Company table:

MySQL
SELECT name FROM Company
ORDER BY name;

Sorting of string data is carried out in lexicographic (alphabetical) order.

Display all the flight information, sorted by the time of departure of the aircraft in ascending order and by time of arrival at the airport in descending order, from the Trip table:

MySQL
SELECT * FROM Trip
ORDER BY time_out, time_in DESC;

In this example, information is sorted at the beginning by departure time. Then, where the departure time coincides, it is sorted by arrival time.

Display all the data on purchases made by Headley Quincey, sorting them in descending order of the value of the goods:

1. First you need to find out the Headley Quincey identifier in the FamilyMembers table:

MySQL
SELECT member_id from FamilyMembers
WHERE member_name = 'Headley Quincey';

2. Derive all the data on purchases made by Headley Quincey from the Payments table:

MySQL
SELECT * FROM Payments
WHERE family_member = (
    SELECT member_id from FamilyMembers
    WHERE member_name = 'Headley Quincey'
);

3. Sort the resulting data in descending order of the value of the goods:

MySQL
SELECT * FROM Payments
WHERE family_member = (
    SELECT member_id from FamilyMembers
    WHERE member_name = 'Headley Quincey'
) ORDER BY unit_price DESC;

Tasks for self-testing

Conditional WHERE operator
Data grouping, aggregate functions