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],...
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
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
contactLastname column 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
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
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;
In the query above, we used
subtotal as the column alias for the
quantityOrdered * priceEach expression and sorted the result set based on the
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
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
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: