Summary: in this tutorial, you will learn how to sort the result set using MySQL ORDER BY with various sorting techniques.

MySQL ORDER syntax

When you use the SELECT statement to query the data from tables, the result set is not sorted in a specific order. In order to sort  the result set, you use the ORDER BY clause. The ORDER BY clause allows you to:

  • Sort a result set using single column or multiple columns.
  • Sort a result set using different columns in different directions, either ascending or descending order.

The following illustrates the syntax of the MySQL ORDER BY clause:

1
2
3
SELECT col1, col2,...
FROM tbl
ORDER BY col1 [ASC|DESC], col2 [ASC|DESC],...

ASC stands for ascending and DESC stands for descending.

MySQL ORDER BY examples

Let’s practice with some examples to see how the ORDER BY clause works.

The following query sorts the result set by contact last name in ascending order.

1
2
3
4
SELECT contactLastname,
       contactFirstname
FROM customers
ORDER BY contactLastname;

mysql-order-by-asc

The MySQL ORDER BY sorts the result set in the ascending order by default if you don’t explicitly using ASC or DESC. If you want to sort the customers by contact last name in descending order, you need to use DESC after the column name in the ORDER BY clause:

1
2
3
4
SELECT contactLastname,
       contactFirstname
FROM customers
ORDER BY contactLastname DESC;

mysql-order-by-desc

If you want to sort the customer by contact last name in descending order and contact first name in ascending order, you can use the follow query:

1
2
3
4
5
SELECT contactLastname,
       contactFirstname
FROM customers
ORDER BY contactLastname DESC,
         contactFirstname ASC;

mysql-order-by-asc-desc

In the query above, MySQL ORDER sorts the result set by contact last name in descending order first, and then sorts the sorted result set by contact first name in ascending order to produce the final result set.

MySQL ORDER BY clause allows you to sort the result set based on expression. In the following example, we will query order line items from the orderdetails table. In the query, we calculate the subtotal for each line item and sort the result set using  the subtotal and order number.

1
2
3
4
5
SELECT ordernumber,
       quantityOrdered * priceEach
FROM orderdetails
ORDER BY ordernumber,
         quantityOrdered * priceEach;

mysql-order-by-expression

To make the query more readable, you can use alias, and sort the result based on the alias.

1
2
3
4
5
SELECT orderNumber,
       quantityOrdered * priceEach AS subTotal
FROM orderdetails
ORDER BY orderNumber,
         subTotal;
1

In the above query, we used subtotal as the alias for the expression quantityOrdered * priceEach, and sorted the result set based on the subtotal alias. The MySQL ORDER BY clause enables you to define your own custom sort order for the values in a column using FIELD() function. For example, if you want to sort the orders based on the following order’s status sequence:

  • 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:

1
2
3
4
5
6
7
8
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 the result set using MySQL ORDER BY clause.

From http://www.mysqltutorial.org/