Sorting data

Now that we have looked at different techniques of retrieving the data, let us look at how the data can be represented in a more ordered way. When we execute a SELECT statement, the data is retrieved in the order in which it exists in the database. This would be the order in which the data is stored; therefore, it is not a good idea to depend upon MariaDB's default sorting. MariaDB provides an explicit mechanism for sorting data; we can use the ORDER BY clause with the SELECT statement and sort the data as needed. To understand how sorting can be of help, let us begin by querying the students table and only retrieving the first_name column:

In the first example, we are going by MariaDB's default sort, and this would give us the data that is being returned based on the order of the insert:

In this example, we are ordering the data based on the first_name column. The ORDER BY clause by default sorts in ascending order, so the data would be sorted in an ascending alphabetical order and if the first character of one or more strings is the same, then the data is sorted by the second character, which is why Jane comes before John. To explicitly mention the sort order as ascending, we can use the keyword asc after the column name:

In this example, we are again ordering the data based on the first_name column and the ORDER BY clause has been supplied with desc, we are setting the sort direction to descending, which denotes that the data has been sorted in a descending order. MariaDB also provides a multi-column sort, which is a sort within a sort. To perform a multi-column sort, we would specify the column names after the ORDER BY clause separated by comma (,). The way the multi-column works is, the data would be first sorted by the first column that is mentioned in the ORDER BY clause, and then the dataset that has already been sorted by the first column is again sorted by the next column and the data is returned back. As a muti-column sort performs sorting on multiple levels, the order of columns will determine the way the data is ordered. To perform this example, let us insert another row with the student name John Dane and the student ID being 4, the reason for using John Dane is to make sure that there are more than one students that share the first name of John (John Doe and John Dane) and the last name of Dane (Jane Dane and John Dane) exclusively:

In this example, we are retrieving the last_name and first_name columns from the students table and are first ordering the data by "last_name" and then reordering the previously ordered dataset by first_name. We are not restricted by the ORDER BY clause to use only the columns being used for the sort. This will only help us sort the data in the correct direction.