MySQL ORDER BY Clause with Examples


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_dateorder_numbercustomeramount, and salesperson, as shown below:

order_dateorder_numbercustomeramountsalesperson
11/7/2019#200001Erick Little1600.50Lisa Ramsey
11/7/2019#200002Ronald Houston1050.50William Barnett
11/7/2019#200003Sandy Howard480.00Iris Wilkerson
11/8/2019#200004Ronald Houston440.00Lisa Ramsey
11/8/2019#200005Nicole Morton570.00Lisa Ramsey
11/9/2019#200006Ronald Houston850.00William Barnett
11/9/2019#200007Gloria Harvey420.00Iris Wilkerson
11/10/2019#200008Margie Fowler230.00Iris Wilkerson
12/2/2019#200009Sandy Howard130.00Iris Wilkerson
12/2/2019#200010Margie Fowler285.00Lisa Ramsey
12/3/2019#200011Erick Little550.50William Barnett
12/4/2019#200012Ronald Houston340.50William Barnett
12/6/2019#200013Nicole Morton315.50Lisa Ramsey
12/8/2019#200014Erick Little460.00William 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.

Leave a Reply

Your email address will not be published. Required fields are marked *