MySQL ORDER BY

Created with Sketch.
MySQL Tutorial

MySQL ORDER BY

Summary: in this tutorial, you will learn how to sort the rows in a result set using the MySQL ORDER BY clause.

Introduction to the MySQL ORDER BY clause

When you use the SELECT statement to query data from a table, the order of rows in the result set is unspecified. To sort the rows in the result set, you add the ORDER BY clause to the SELECT statement.

The following illustrates the syntax of the ORDER BY clause:

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

Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the one or more columns that you want to sort after the ORDER BY clause.

The ASC stands for ascending and the DESC stands for descending. You use ASC to sort the result set in ascending order and DESC to sort the result set in descending order respectively.

This ORDER BY clause sorts the result set by the values in the column1 in ascending order:

ORDER BY column1 ASC;

Code language: SQL (Structured Query Language) (sql)

And this ORDER BY clause sorts the result set by the values in the column1 in descending order:

ORDER BY column1 DESC;

Code language: SQL (Structured Query Language) (sql)

By default, the ORDER BY clause uses ASC if you don’t explicitly specify any option. Therefore, the following ORDER BY clauses are equivalent:

ORDER BY column1 ASC;

Code language: SQL (Structured Query Language) (sql)

and

ORDER BY column1;

Code language: SQL (Structured Query Language) (sql)

If you want to sort the result set by multiple columns, you specify a comma-separated list of columns in the ORDER BY clause:

ORDER BY
column1,
column2;

Code language: SQL (Structured Query Language) (sql)

In this case, the ORDER BY clause sorts the result set by column1 in ascending order first and sorts the sorted result set by column2 in ascending order.

It is possible to sort the result set by a column in ascending order and then by another column in descending order:

ORDER BY
column1 ASC,
column2 DESC;

Code language: SQL (Structured Query Language) (sql)

In this case, the ORDER BY clause:

  • First, sort the result set by the values in the column1 in ascending order.
  • Then, sort the sorted result set by the values in the column2  in descending order. Note that the order of values in the column1 will not change in this step, only the order of values in the column2 changes.

When executing the SELECT statement with an ORDER BY clause, MySQL always evaluates the ORDER BY clause after the FROM and SELECT clauses:

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.

SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname;

Code language: SQL (Structured Query Language) (sql)

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:

SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC;

Code language: SQL (Structured Query Language) (sql)

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:

SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC ,
contactFirstname ASC;

Code language: SQL (Structured Query Language) (sql)

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.

order_details_table

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.

SELECT
orderNumber,
orderlinenumber,
quantityOrdered * priceEach
FROM
orderdetails
ORDER BY
quantityOrdered * priceEach DESC;

Code language: SQL (Structured Query Language) (sql)

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

SELECT
orderNumber,
orderLineNumber,
quantityOrdered * priceEach AS subtotal
FROM
orderdetails
ORDER BY subtotal DESC;

Code language: SQL (Structured Query Language) (sql)

+-------------+-----------------+----------+
| 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':

SELECT FIELD('A', 'A', 'B','C');

Code language: SQL (Structured Query Language) (sql)

Output:

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

Code language: plaintext (plaintext)

And the following example returns 2:

SELECT FIELD('B', 'A','B','C');

Code language: SQL (Structured Query Language) (sql)

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:

SELECT
orderNumber, status
FROM
orders
ORDER BY FIELD(status,
'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');

Code language: SQL (Structured Query Language) (sql)

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

SELECT
firstName, lastName, reportsTo
FROM
employees
ORDER BY reportsTo;

Code language: SQL (Structured Query Language) (sql)

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:

SELECT
firstName, lastName, reportsTo
FROM
employees
ORDER BY reportsTo DESC;

Code language: SQL (Structured Query Language) (sql)

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
0

Leave a Reply

Your email address will not be published. Required fields are marked *

©  2025 python tutorials.  python-tutorials.in