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:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
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 theDESC
option to sort the result set in descending order. - The
ORDER BY
clause is evaluated after theFROM
andSELECT
clauses. - In MySQL,
NULL
is lower than non-NULL values