MySQL Sort Results with ORDER BY Statement

While working with MySQL queries, the results are obtained in the same sequence as the records inserted into the schema utilizing the SELECT command. It’s the standard order for sorting. You would be aiming at how we might arrange our query result. Sorting is re-arranging the outputs of our query in a defined manner. Sorting may be done on one field or more than one field. The ORDER BY statement is being used to arrange the query results in an ascending or descending order in MySQL. The ORDER BY statement organizes data by default in go-up order if ASC or DESC is not specified. The DESC term is being used to organize the data in descending way.

Syntax:

>> SELECT * FROM table_name ORDER BY expression ASC|DESC
>>SELECT expression FROM table_name ORDER BY expression ASC|DESC
>> SELECT expression FROM table_name WHERE condition ORDER BY expression ASC|DESC

Let’s have a glimpse at the explanation of a query.

Table_name: Name of a table to get data from
Expression: Name of a column to be retrieved or name of a column used to arrange data.
ASC: Used to categorize data in ascending order. It is optional.
DESC: Used to arrange data in descending order. It is optional
WHERE condition: It is an optional constraint to be used.

Get started with opening the MySQL command-line client shell to start working on sorting. It may ask for your MySQL password. Type your password and tap Enter to continue.

Example: Sort without ORDER BY (ASC or DESC) clause:

To elaborate sorting with the ORDER BY clause, we have been starting our first example without using the ORDER BY clause. We have a table ‘teacher’ in the schema ‘data’ of MySQL with some records in it. When you want to fetch the data from this table, you will get it as it is, as it was inserted in the table without performing extra sorting, as presented below.

>> SELECT * FROM data.teacher;

Example: Sort with ORDER BY Column Name without ASC|DESC:

Taking the same table with a little change in the SELECT query. We have specified the name of a column according to which the whole table will get sorted. We have been using the column ‘id’ to sort the table. As we haven’t defined the sort type, e.g., Ascending or descending, that’s why it will be automatically sorted in ascending order of ‘id’.

>> SELECT * FROM data.teacher ORDER BY id;

Let’s sort the same table without using ASC or DESC expression in the SELECT statement while using another column. We will be sorting this table ORDER BY the column ‘subject’. All the data in the column ‘subject’ will get sorted alphabetically first; then, the whole table will be sorted according to it.

>> SELECT * FROM data.teacher ORDER BY subject;

Now. We will be sorting the table ‘teacher’, according to the column ‘qualification’. This query will sort the column ‘qualification’ alphabetically first. After that, all the records get sorted by this column as below.

>> SELECT * FROM data.teacher ORDER BY qualification;

You may also fetch the specified column data from the table with the ORDER BY clause. Let’s display the three-column data from the table ‘teacher’ and sort this data according to the column ‘firstname’. We will be getting three columns sorted record as shown.

>> SELECT firstname, subject, qualification FROM data.teacher ORDER BY firstname;

Example: Sort with ORDER BY Single Column Name with ASC|DESC:

Now, we will be performing the same query with a little change in its syntax. We will specify the sorting type while defining the column name in the query. Let us fetch the record of four columns: firstname, lastname, subject, and qualification from a table ‘teacher’ while sorting this record according to the column ‘firstname’ in ascending order. This means that the column ‘firstname’ will be sorted in ascending order first then all the data regarding it will get sorted.

>> SELECT firstname, lastname, subject, qualification FROM data.teacher ORDER BY firstname ASC;

According to the descending order of column ‘firstname’, Sorting the same record of four columns’ is as follows.

>> SELECT firstname, lastname, subject, qualification FROM data.teacher ORDER BY subject DESC;

Fetching the fully-fledge table ‘teacher’ while using the column ‘firstname’ in ascending order is as follows.

>> SELECT * FROM data.teacher ORDER BY firstname ASC;

Let’s retrieve the whole table by the descending order of column ‘id’ as below.

>> SELECT * FROM data.teacher ORDER BY id DESC;

Example: Sort with ORDER BY Multiple Column Name with ASC|DESC:

Yes! You can easily sort your table with multiple columns. You just need to specify the ORDER BY clause while a comma separates each column name with its sorting type. Let’ take a glimpse of a simple example. We have been selecting four-column data from a table. Firstly, this data will be sorted according to the column ‘id’ in descending order then into descending order by the column ‘firstname’.

>> SELECT id, firstname, subject, qualification FROM data.teacher ORDER BY id DESC, firstname ASC;

Example: Sort with ORDER BY with WHERE clause:

As we know that the WHERE clause is being used for performing some conditions on data. We can easily sort our data while using the WHERE clause and fetch it according to it. We have performed a simple query in which we have fetched all records from the table ‘teacher’ where the ‘id’ is greater than 4 and the ‘qualification’ of a teacher is ‘Mphil’. This query will fetch the records of teachers whom qualification is equaled to MPhil, and their ‘id’ is not less than 5. After that, this data will b sorted in descending order of the ‘ids’ of teachers as shown in the image.

>> SELECT * FROM data.teacher WHERE id > 4 AND qualification = ‘MPhil’ ORDER BY id DESC;

If you want to fetch the specified columns from a table, you can also do that. Let’s do that as below.

>> SELECT id, firstname, subject, qualification FROM data.teacher WHERE id > 2 AND id < 11 AND subject = ‘Math’ ORDER BY qualification DESC;

Conclusion:

We have done almost all the examples to learn the ORDER BY clause for sorting the records. I hope this article will help you to sort results in MySQL.