How to Use the ORDER BY Clause in MySQL
In this tutorial, you'll learn how to retrieve records in a table from a MySQL database sorted in ascending or descending order.
To retrieve the records sorted in ascending or descending order, you use the MySQL ORDER BY clause along with SELECT.
The ORDER BY clause is as follows:
SELECT column1,column2,column3,...,columnN FROM table ORDER BY column1, column2,... ASC/DESC;
If you don't specify ASC or DESC in the ORDER BY clause, MySQL places records in ascending order by default.
If you need to retrieve the records sorted in descending order, use the DESC keyword.
ORDER BY Clause Examples
Demo data:
Suppose you have the table named orders, which consists of five columns named order_date, order_number, customer, amount, and salesperson, as shown below:
| order_date | order_number | customer | amount | salesperson |
| 11/7/2019 | #200001 | Erick Little | 1600.50 | Lisa Ramsey |
| 11/7/2019 | #200002 | Ronald Houston | 1050.50 | William Barnett |
| 11/7/2019 | #200003 | Sandy Howard | 480.00 | Iris Wilkerson |
| 11/8/2019 | #200004 | Ronald Houston | 440.00 | Lisa Ramsey |
| 11/8/2019 | #200005 | Nicole Morton | 570.00 | Lisa Ramsey |
| 11/9/2019 | #200006 | Ronald Houston | 850.00 | William Barnett |
| 11/9/2019 | #200007 | Gloria Harvey | 420.00 | Iris Wilkerson |
| 11/10/2019 | #200008 | Margie Fowler | 230.00 | Iris Wilkerson |
| 12/2/2019 | #200009 | Sandy Howard | 130.00 | Iris Wilkerson |
| 12/2/2019 | #200010 | Margie Fowler | 285.00 | Lisa Ramsey |
| 12/3/2019 | #200011 | Erick Little | 550.50 | William Barnett |
| 12/4/2019 | #200012 | Ronald Houston | 340.50 | William Barnett |
| 12/6/2019 | #200013 | Nicole Morton | 315.50 | Lisa Ramsey |
| 12/8/2019 | #200014 | Erick Little | 460.00 | William Barnett |
Example 1: ORDER BY a single column
The following example retrieves all records form the orders table sorted by the order_date column:
SELECT * FROM orders ORDER BY order_date;
MySQL returns the result set sorted be in ascending order by default because ASC or DESC is not specified.
You can retrieve the records and place them in descending order, as the following example shows:
SELECT * FROM orders ORDER BY order_date DESC;
Example 2: ORDER BY multiple columns
You can sort more than one column in ascending or descending order by using the ORDER BY clause.
You can retrieve all records from a given table and sort them by more than one column, as the following example shows:
SELECT * FROM orders ORDER BY customer, order_date.
The above SQL query retrieves all records from the order table sorted in ascending order by the customer column and then the order_date column.
You can retrieve all records from a given table, sorted by multiple columns in descending, as the following example shows:
SELECT * FROM orders ORDER BY customer DESC, order_date DESC.
You can also sort records by multiple columns in ascending order and then in descending order, as the following example shows:
SELECT * FROM orders ORDER BY customer ASC, order_date DESC
The above SQL query retrieves all records from the orders table sorted in ascending order by the customer column and then sorted in descending order by the order_date column.
Example 3: ORDER BY and WHERE clause
You can also use the ORDER BY clause with a WHERE clause.
The syntax of the ORDER BY clause and the WHERE clause in the SELECT statement is as follows:
SELECT column1, column2... FROM table WHERE where_conditions ORDER BY column1, column2 ASC/DESC;
Here is an example:
SELECT * FROM orders WHERE customer="Erick Little" ORDER BY order_date DESC;
The above SQL query retrieves all the records where the customer is Erick Little, sorted in descending order by the order_date column.
In this tutorial, you've learned how to use the MySQL ORDER BY clause along with SELECT.
You can use the ORDER BY clause to retrieve records from a table sorted in ascending or descending order by one or multiple columns.