Summary: in this tutorial, you will learn how to sort a result set by using MySQL ORDER BY clause.
Introduction to MySQL ORDER BY clause
When you use the SELECT statement to query data from a table, the result set is not sorted in a specific order. To sort the result set, you use the ORDER BY clause. The ORDER BY clause allows you to:
Sort a result set by a single column or multiple columns.
Sort a result set by different columns in ascending or descending order.
The following illustrates the syntax of the ORDER BY clause:
SELECT col1, col2,... FROM tbl ORDER BY col1 [ASC|DESC], col2 [ASC|DESC],...
The ASC stands for ascending and the DESC stands for descending. By default, the ORDER BY clause sorts the result set in ascending order if you don’t specify ASC or DESC explicitly
Let’s practice with some examples of using the ORDER BY clause.
MySQL ORDER BY examples
The following query selects contacts from the customers table and sorts the contacts by last name in ascending order.
SELECT contactLastname, contactFirstname FROM customers ORDER BY contactLastname;
If you want to sort the contact by last name in descending order, you specify the DESC after the contactLastnamecolumn in the ORDER BY clause as the following query:
SELECT contactLastname, contactFirstname FROM customers ORDER BY contactLastname DESC
If you want to sort the contacts by last name in descending order and first name in ascending order, you specify both DESC and ASC in the corresponding column as follows:
SELECT contactLastname, contactFirstname FROM customers ORDER BY contactLastname DESC, contactFirstname ASC;
In the query above, the ORDER BY clause sorts the result set by last name in descending order first, and then sorts the sorted result set by first name in ascending order to produce the final result set.
MySQL ORDER BY sort by an expression example
The ORDER BY clause also allows you to sort the result set based on an expression. The following query selects the order line items from the orderdetails table. It calculates the subtotal for each line item and sorts the result set based on the order number and subtotal.
SELECT ordernumber, quantityOrdered * priceEach FROM orderdetails ORDER BY ordernumber, quantityOrdered * priceEach
To make the result more readable, you can use a column alias, and sort the result based on the column alias.
SELECT orderNumber, quantityOrdered * priceEach AS subTotal FROM orderdetails ORDER BY orderNumber, subTotal;
| 1 |
In the query above, we used subtotal as the column alias for the quantityOrdered * priceEach expression and sorted the result set based on the subtotal alias.
If you use a function that returns a value whose data type is different from the column’s and sort the result based on the alias, the ORDER BY clause will sort the result set based on the return type of the function, which may not work as expected.
For example, if you use the DATE_FORMAT function to format the date values and sort the result set based on the strings returned by the DATE_FORMAT function, the order is not always correct. For more information, check it out the example in the DATE_FORMAT function tutorial.
MySQL ORDER BY with customer sort order
The ORDER BY clause enables you to define your own custom sort order for the values in a column using the FIELD() function. For example, if you want to sort the orders based on the following status by the following order:
In Process
On Hold
Cancelled
Resolved
Disputed
Shipped
You can use the FIELD() function to map those values to a list of numeric values and use the numbers for sorting; See the following query: