In this tutorial, we will learn about the MySQL ORDER BY clause. Ordering and sorting as a feature in tables and databases are very important. This helps you analyze information and provide you with valuable insights into it. You could be the CEO of the company and sort the profit of every product you sell to find which product yields more profits. Or, you could be a school teacher wanting to determine the rank of the students by sorting their percentage.
MySQL provides you with the
ORDER BY clause to sort data in a table in ascending or descending order. You can get the sorted data by using the ORDER BY clause with the
Syntax of MySQL ORDER BY Clause
SELECT expression FROM table_name ORDER BY column1_name [ASC|DESC], column2_name [ASC|DESC]...;
By default, ORDER BY sorts data in the ascending order. However, you can explicitly mention sorting in ascending order by using ASC after mentioning the column name. To sort in descending order, you should use DESC after mentioning the column name.
Examples of MySQL ORDER BY Clause
To understand this clause better, let’s look at some of the examples of the ORDER BY clause.
1. Using ORDER BY clause for one column
Consider the following Students table.
Let us try to sort the students in descending order of DaysPresent. We do this using the SELECT query,
SELECT * FROM Students ORDER BY DaysPresent DESC;
The output we get is,
ORDER BY clause also works for text. You can sort alphabets and words too. So, how about arranging the records of students in ascending order of their LastName? We will use one of the following queries,
SELECT * FROM Students ORDER BY LastName ASC;
SELECT * FROM Students ORDER BY LastName;
We get the output as follows:
2. MySQL ORDER BY clause for multiple columns
You can sort multiple columns using the
ORDER BY clause too. Let us consider the ConferenceGuests table.
It is very important to understand how sorting with multiple columns is done. If you want to sort the result by multiple columns, you specify them as:
ORDER BY column1_name, column2_name;
So, how does the MySQL ORDER BY clause work?
ORDER BYfirst sorts the column specified in column1_name, in this case, in the ascending order.
- Next, the
ORDER BYclause takes the sorted set in the above step and sorts it again using the column specified in column2_name. It happens such that the order of the column in column1_name does not change in this step.
Let us take an example to understand this better. How about sorting the Country and then the State column alphabetically?
SELECT * FROM ConferenceGuests ORDER BY Country, State;
The output you get is:
So what happens is that the Country column gets sorted first and then that resulting sorted set gets sorted such that the Country column does not change and the State column gets sorted with respect to the Country column.
To understand better, how about sorting the Country column in ascending order and then the State column in descending order?
SELECT * FROM ConferenceGuests ORDER BY Country, State DESC;
You get the following output,
Now, as you can see, the Country column gets sorted in ascending order and now, the State column gets sorted in the descending order with respect to the Country column.
3. WHERE Clause With ORDER BY Clause
You can use the
WHERE clause along with the
ORDER BY clause. Let us look at an example of this from the Students table.
How about sorting the City column in the Students table and getting records of only those students who were present for more than 75 days? We use the following query:
SELECT * FROM Students WHERE DaysPresent>75 ORDER BY City;
We get the output as follows,
As you can see, we get our sorted result-set in which only those records of students are shown whose have been present for more than 75 days.
ORDER BY clause is widely used and finds use in various data analysis applications in large-scale databases and tables. I would highly recommend you check out the following links.