MySQL ORDER BY Keyword

What is MySQL ORDER BY?

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword . The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

ORDER BY Example

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” column:

Example

SELECT * FROM Customers
ORDER BY Country;

ORDER BY DESC Example

The following SQL statement selects all customers from the “Customers” table, sorted DESCENDING by the “Country” column:

Example

SELECT * FROM Customers
ORDER BY Country DESC;

ORDER BY Several Columns Example

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

Example

SELECT * FROM Customers
ORDER BY Country, Customer Name;

ORDER BY Several Columns Example 2

The following SQL statement selects all customers from the “Customers” table, sorted ascending by the “Country” and descending by the “CustomerName” column:

Example

SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

MySQL ORDER BY examples

We’ll use the customers table from the sample database for the demonstration.

A) Using MySQL ORDER BY clause to sort the result set by one column example

The following query uses the ORDER BY clause to sort the customers by their last names in ascending order.

<code>SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Accorti         | Paolo            |
| Altagar,G M     | Raanan           |
| Andersen        | Mel              |
| Anton           | Carmen           |
| Ashworth        | Rachel           |
| Barajas         | Miguel           |
...Code language: plaintext (plaintext)

If you want to sort customers by the last name in descending order, you use the DESC after the contactLastname column in the ORDER BY clause as shown in the following query:

<code>SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname DESC;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Ouptut:

+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Young           | Jeff             |
| Young           | Julie            |
| Young           | Mary             |
| Young           | Dorothy          |
| Yoshido         | Juri             |
| Walker          | Brydey           |
| Victorino       | Wendy            |
| Urs             | Braun            |
| Tseng           | Jerry            |
....Code language: plaintext (plaintext)

B) Using MySQL ORDER BY clause to sort the result set by multiple columns example

If you want to sort the customers by the last name in descending order and then by the first name in ascending order, you specify both  DESC and ASC in these respective columns as follows:

<code>SELECT 
    contactLastname, 
    contactFirstname
FROM
    customers
ORDER BY 
	contactLastname DESC , 
	contactFirstname ASC;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Young           | Dorothy          |
| Young           | Jeff             |
| Young           | Julie            |
| Young           | Mary             |
| Yoshido         | Juri             |
| Walker          | Brydey           |
| Victorino       | Wendy            |
| Urs             | Braun            |
| Tseng           | Jerry            |
| Tonini          | Daniel           |
...Code language: plaintext (plaintext)

In this example, the ORDER BY  clause sorts the result set by the last name in descending order first and then sorts the sorted result set by the first name in ascending order to make the final result set.

C) Using MySQL ORDER BY clause to sort a result set by an expression example

See the following orderdetails table from the sample database.

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 subtotal.

<code>SELECT 
    orderNumber, 
    orderlinenumber, 
    quantityOrdered * priceEach
FROM
    orderdetails
ORDER BY 
   quantityOrdered * priceEach DESC;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

+-------------+-----------------+-----------------------------+
| orderNumber | orderlinenumber | quantityOrdered * priceEach |
+-------------+-----------------+-----------------------------+
|       10403 |               9 |                    11503.14 |
|       10405 |               5 |                    11170.52 |
|       10407 |               2 |                    10723.60 |
|       10404 |               3 |                    10460.16 |
|       10312 |               3 |                    10286.40 |
...Code language: plaintext (plaintext)

To make the query more readable, you can assign the expression in the SELECT clause a column alias and use that column alias in the ORDER BY clause as shown in the following query:

<code>SELECT 
    orderNumber,
    orderLineNumber,
    quantityOrdered * priceEach AS subtotal
FROM
    orderdetails
ORDER BY subtotal DESC;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

+-------------+-----------------+----------+
| orderNumber | orderLineNumber | subtotal |
+-------------+-----------------+----------+
|       10403 |               9 | 11503.14 |
|       10405 |               5 | 11170.52 |
|       10407 |               2 | 10723.60 |
|       10404 |               3 | 10460.16 |
|       10312 |               3 | 10286.40 |
|       10424 |               6 | 10072.00 |
|       10348 |               8 |  9974.40 |
|       10405 |               3 |  9712.04 |
|       10196 |               5 |  9571.08 |
|       10206 |               6 |  9568.73 |
 ...Code language: plaintext (plaintext)

In this example, we use subtotal as the column alias for the expression quantityOrdered * priceEach and sort the result set by the subtotal alias. Since MySQL evaluates the SELECT clause before the ORDER BY clause, you can use the column alias specified in the SELECT clause in the ORDER BY clause.

Using MySQL ORDER BY clause to sort data using a custom list

The FIELD() function has the following syntax:

FIELD(str, str1, str2, ...)Code language: SQL (Structured Query Language) (sql)

The FIELD() function returns the position of the str in the str1, str2, … list. If the str is not in the list, the FIELD() function returns 0. For example, the following query returns 1 because the position of the string ‘A’ is the first position on the list 'A', 'B', and 'C':

<code>SELECT FIELD('A', 'A', 'B','C');</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

+--------------------------+
| FIELD('A', 'A', 'B','C') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

And the following example returns 2:

<code>SELECT FIELD('B', 'A','B','C');</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

+-------------------------+
| FIELD('B', 'A','B','C') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

Let’s take a more practical example.

See the following orders table from the sample database.

Suppose that you want to sort the sales orders based on their statuses in the following order:

• In Process
• On Hold
• Canceled
• Resolved
• Disputed
• Shipped

To do this, you can use the FIELD() function to map each order status to a number and sort the result by the result of the FIELD() function:

<code>SELECT 
    orderNumber, status
FROM
    orders
ORDER BY FIELD(status,
        'In Process',
        'On Hold',
        'Cancelled',
        'Resolved',
        'Disputed',
        'Shipped');</code><small>Code language: SQL (Structured Query Language) (sql)</small>

+-------------+------------+
| orderNumber | status     |
+-------------+------------+
|       10425 | In Process |
|       10421 | In Process |
|       10422 | In Process |
|       10420 | In Process |
|       10424 | In Process |
|       10423 | In Process |
|       10414 | On Hold    |
|       10401 | On Hold    |
|       10334 | On Hold    |
|       10407 | On Hold    |
...Code language: plaintext (plaintext)

MySQL ORDER BY and NULL

In MySQL, NULL comes before non-NULL values. Therefore, when you the ORDER BY clause with the ASC option, NULLs appear first in the result set.

For example, the following query uses the ORDER BY clause to sort employees by values in the reportsTo column:

<code>SELECT 
    firstName, lastName, reportsTo
FROM
    employees
ORDER BY reportsTo;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

+-----------+-----------+-----------+
| firstName | lastName  | reportsTo |
+-----------+-----------+-----------+
| Diane     | Murphy    |      NULL |
| Mary      | Patterson |      1002 |
| Jeff      | Firrelli  |      1002 |
| William   | Patterson |      1056 |
| Gerard    | Bondur    |      1056 |
...Code language: plaintext (plaintext)

However, if you use the ORDER BY with the DESC option, NULLs will appear last in the result set. For example:

<code>SELECT 
    firstName, lastName, reportsTo
FROM
    employees
ORDER BY reportsTo DESC;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

+-----------+-----------+-----------+
| firstName | lastName  | reportsTo |
+-----------+-----------+-----------+
| Yoshimi   | Kato      |      1621 |
| Leslie    | Jennings  |      1143 |
| Leslie    | Thompson  |      1143 |
| Julie     | Firrelli  |      1143 |
| ....
| Mami      | Nishi     |      1056 |
| Mary      | Patterson |      1002 |
| Jeff      | Firrelli  |      1002 |
| Diane     | Murphy    |      NULL |
+-----------+-----------+-----------+
23 rows in set (0.00 sec)Code language: plaintext (plaintext)

Summary

• Use the ORDER BY clause to sort the result set by one or more columns.
• Use the ASC option to sort the result set in ascending order and the DESC option to sort the result set in descending order.
• The ORDER BY clause is evaluated after the FROM and SELECT clauses.
• In MySQL, NULL is lower than non-NULL values