MySQL ORDER BY Clause Tutorial

In this tutorial, you'll learn how to use the MySQL ORDER BY clause to sort the rows in a result set.

The MySQL ORDER BY clause: An overview

The order of rows returned by a SELECT statement query from a table is undefined. Inserting the ORDER BY clause after the SELECT statement will sort the rows of the result set in the order specified.

The syntax of the ORDER BY clause is shown below:

SELECT 
   column1, column2
FROM 
   table_name
ORDER BY 
   column1 [ASC|DESC], 
   column2 [ASC|DESC],
   ...;

In the above ORDER BY clause syntax, you name the column(s) that will be used to determine the sort order.

The ASC indicates ascending order which is the default order, whereas the DESC indicates a descending one. Ascending (ASC) and descending (DESCENDING) are used to sort the results.

The following ORDER BY clause arranges the output in ascending order of the values in column1:

ORDER BY column1 ASC;

Since there is no inherent order between rows in a relational database, we must instruct MySQL to sort the results before we can use them in any meaningful way.

MySQL ORDER BY Clause Examples

It's possible you'd like to get back an index of all the customers in some customer database, ordered by their customer names. Here is what you would enter:

SELECT customer_id, first_name, last_name 
   FROM Customers 
   order by first_name;

Output:

customer_id	first_name	last_name
5	Betty	Doe
3	David	Robinson
1	John	Doe
4	John	Reinhardt
2	Robert	Luna

The above result is in ascending order which is the default order for the ORDER BY clause.

To get the same result in descending order, we will specify the SQL query as follows:

SELECT customer_id, first_name, last_name 
    FROM Customers 
    order by first_name DESC;

Output:

customer_id	first_name	last_name
2	Robert	Luna
1	John	Doe
4	John	Reinhardt
3	David	Robinson
5	Betty	Doe

Now our result is in descending order for the column first_name. But the last_name column is not in order. So if you want to sort both columns (first_name, last_name) in descending order you will have to specify the DESC clause after each column name separated by a comma. Below is an example:

SELECT customer_id, first_name, last_name 
           FROM Customers 
           order by first_name desc, 
                    last_name desc

Output:

customer_id	first_name	last_name
2	Robert	Luna
4	John	Reinhardt
1	John	Doe
3	David	Robinson
5	Betty	Doe

We have the age column in the customers table. So now we will sort the result on the age colunm which is a numeric column. Below is the example:

select * from customers order by age;

Output:

customer_id	first_name	last_name	age	country
6	Joseph	Sebastin		USA
2	Robert	Luna	22	USA
3	David	Robinson	22	UK
4	John	Reinhardt	25	UK
5	Betty	Doe	28	UAE
1	John	Doe	31	USA

But in the above result set, you can see that the row with the null value of the age column is displayed first. So if you want to sort the result but want the null value in last, use the nulls last keyword after the column name. The following is an example:

select * from customers order by age nulls last;

Output:

customer_id	first_name	last_name	age	country
2	Robert	Luna	22	USA
3	David	Robinson	22	UK
4	John	Reinhardt	25	UK
5	Betty	Doe	28	UAE
1	John	Doe	31	USA
6	Joseph	Sebastin		USA

Points to Remember for ORDER BY Clause

Sort the output by one or more columns using the ORDER BY clause.
For an ascending sort, select ASC and a descending sort, select DESC.
The FROM and SELECT clauses are examined before the ORDER BY clause is considered.
MySQL ranks NULL as a lesser value than anything else.

In this tutorial, you have learned MySQL ORDER BY clause to sort the table data returned by the SELECT statement.