How to Use the ORDER BY Clause in MySQL with Examples
In this tutorial, you will learn to retrieve rows of a table sorted in ascending or descending order from a MySQL database.
To get the rows sorted in ascending or descending order, we use MySQL ORDER BY clause along with a SELECT statement.
The syntax of ORDER BY clause is as follows:
SELECT column1,column2,column3,...,columnN FROM table ORDER BY column1, column2,... ASC/DESC;
If you do not specify ASC or DESC in the ORDER BY clause, MySQL will return rows sorted in ascending order by default. So, if you want to get the rows sorted in descending order, specify the DESC keyword.
ORDER BY Clause Examples
Demo data:
Suppose you have a table named orders, and it consists of 5 columns: 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 SQL query retrieves all rows of the orders table sorted by the order_date column:
SELECT * FROM orders ORDER BY order_date;
Since ASC or DESC is not specified, MySQL will return a result set sorted in ascending order by default.
To get the rows of the order table and sort them in descending order, write an SQL query like this:
SELECT * FROM orders ORDER BY order_date DESC;
Example 2: ORDER BY multiple columns
You can also use the ORDER BY clause to sort rows in ascending or descending order by multiple columns.
Here is an example of retrieving all rows from the orders table and sorting them by the customer and order_date columns:
SELECT * FROM orders ORDER BY customer, order_date.
This SQL query retrieves all rows from the order table, sorted in ascending order by the customer column and the order_date column.
To get all the rows sorted in descending order by both columns from the orders table, write a SQL query like this:
SELECT * FROM orders ORDER BY customer DESC, order_date DESC.
You can also retrieve rows and sort them in ascending and then descending order by multiple columns, as shown in the following example:
SELECT * FROM orders ORDER BY customer ASC, order_date DESC
Example 3: ORDER BY and WHERE clause
You can also use the ORDER BY clause together with a WHERE clause.
The syntax for the ORDER BY and WHERE clauses 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;
This SQL query retrieves rows where the customer is Erick Little, sorted in descending order by the order_date column.
Summary
In this tutorial, you have learned how to use the MySQL ORDER BY clause with a SELECT statement.
You can use the ORDER BY clause to retrieve rows from a table sorted in ascending or descending order by one or more columns.