MySQL - ORDER BY Clause

MySQL - ORDER BY Clause

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 inascending 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 thecontactLastname 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  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;

 

 

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:

 

1. In Process

2. On Hold

3. Cancelled

4. Resolved

5. Disputed

6. 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:

 

SELECT orderNumber, status FROM orders

ORDER BY FIELD(status, 'In Process', 'On Hold', 'Cancelled',

                       'Resolved','Disputed', 'Shipped');

 

In this tutorial, we’ve shown you various techniques to sort a result set by using the MySQL ORDER BY clause in the   SELECT statement.