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.
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’.
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.
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.
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.
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.
According to the descending order of column ‘firstname’, Sorting the same record of four columns’ is as follows.
Fetching the fully-fledge table ‘teacher’ while using the column ‘firstname’ in ascending order is as follows.
Let’s retrieve the whole table by the descending order of column ‘id’ as below.
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’.
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.
If you want to fetch the specified columns from a table, you can also do that. Let’s do that as below.
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.