MySQL ORDER BY Clause – How to Sort Records In A Table?

By /
Order By Clause

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 SELECT statement.

Syntax of MySQL ORDER BY Clause

SELECT expression FROM table_name ORDER BY column1_name [ASC|DESC], column2_name [ASC|DESC]...;Code language: SQL (Structured Query Language) (sql)

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.

Students Table - MySQL ORDER BY
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;Code language: SQL (Structured Query Language) (sql)

The output we get is,

Order By Desc Example

The MySQL 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;Code language: SQL (Structured Query Language) (sql)

Or

SELECT * FROM Students ORDER BY LastName;Code language: SQL (Structured Query Language) (sql)

We get the output as follows:

Order By Asc Example

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.

Order By Conferenceguests Table
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;Code language: SQL (Structured Query Language) (sql)

So, how does the MySQL ORDER BY clause work?

  • ORDER BY first sorts the column specified in column1_name, in this case, in the ascending order.
  • Next, the ORDER BY clause 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;Code language: SQL (Structured Query Language) (sql)

The output you get is:

Order By Multiple

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;Code language: SQL (Structured Query Language) (sql)

You get the following output,

MySQL Order By Multiple

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;Code language: SQL (Structured Query Language) (sql)

We get the output as follows,

Order By Where Clause

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.

Conclusion

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.

References