MySQL ORDER BY with practical examples

In this article, we describe how to use MySQL ORDER BY clause to sort the result set of the SELECT statement.

Normally, The rows in a result set returned by a SELECT statement is sorted by the default rules of the database. If you want to change the order of rows int the result set, you can use ORDER BY clause int the SELECT statement.

You can specify columns and Ascending or Descending in the ORDER BY clause.

ORDER BY syntax

In the ORDER BY clause, You can specify one or more columns. The syntax of the ORDER BY clause is as follows:

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

Here:

  • You can specify one or more columns in ORDER BY clause.
  • ASC means ascending order and DESC means descending order. The default value is ASC

ORDER BY explaination

  • ORDER BY column ASC;

    The rows in the result set are sorted by the value in column in ascending order.

  • ORDER BY column DESC;

    The rows in the result set are sorted by the value in column in descending order.

  • ORDER BY column;

    The rows in the result set are sorted by the value in column in ascending order. It is equivalent to ORDER BY column ASC;.

  • ORDER BY column1, column2;

    The rows in the result set are sorted by column1 in ascending order first and sorted by column2 in ascending order second.

    The column1 is the primary sorting column. The rows with the same column1 value are sorted by column2 in ascending order.

  • ORDER BY column1 DESC, column2;

    The rows in the result set are sorted by column1 in descending order first and sorted by column2 in ascending order second.

ORDER BY examples

In the following example, we will use actor table in the Sakila sample database as a demonstration.

Sort by one column

The following SQL statement sorts the actors by their last name in ascending order using the ORDER BY clause.

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name;

+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|       92 | KIRSTEN     | AKROYD       |
|       58 | CHRISTIAN   | AKROYD       |
|      182 | DEBBIE      | AKROYD       |
|      194 | MERYL       | ALLEN        |
|      118 | CUBA        | ALLEN        |
|      145 | KIM         | ALLEN        |
...

Sort by one column in descending order

The following SQL statement sorts the actors by their last name in descending order using the ORDER BY clause.

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name DESC;

+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|      111 | CAMERON     | ZELLWEGER    |
|       85 | MINNIE      | ZELLWEGER    |
|      186 | JULIA       | ZELLWEGER    |
|       63 | CAMERON     | WRAY         |
|      156 | FAY         | WOOD         |
|       13 | UMA         | WOOD         |
|      144 | ANGELA      | WITHERSPOON  |
|       68 | RIP         | WINSLET      |
....

Sort by multiple columns

The following SQL statement sorts the actors by their last name in ascending order and their first name in ascending order.

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name, first_name;

+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|       58 | CHRISTIAN   | AKROYD       |
|      182 | DEBBIE      | AKROYD       |
|       92 | KIRSTEN     | AKROYD       |
|      118 | CUBA        | ALLEN        |
|      145 | KIM         | ALLEN        |
|      194 | MERYL       | ALLEN        |
....

Sort by custom order

Sometimes simply sorting by the value in the column does not meet the requirements, you need to sort values by a custom order. For example, you need to sort the films by the order of ranting: 'G', 'PG', 'PG-13', 'R', 'NC-17'.

For such a demand, it can be understood as sorting by the index position of the elements in the list. You can use the CASE statement or FIELD() function to achieve it.

In the following example, we use the film table in the Sakila sample database as a demonstration.

Suppose you need to sort the films by the order of ranting: 'G', 'PG', 'PG-13', 'R', 'NC-17'.

CASE statement

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;

+---------+-----------------------------+--------+
| film_id | title                       | rating |
+---------+-----------------------------+--------+
|       2 | ACE GOLDFINGER              | G      |
|       4 | AFFAIR PREJUDICE            | G      |
...
|       1 | ACADEMY DINOSAUR            | PG     |
|       6 | AGENT TRUMAN                | PG     |
...
|       7 | AIRPLANE SIERRA             | PG-13  |
|       9 | ALABAMA DEVIL               | PG-13  |
...
|       8 | AIRPORT POLLOCK             | R      |
|      17 | ALONE TRIP                  | R      |
...
|       3 | ADAPTATION HOLES            | NC-17  |
|      10 | ALADDIN CALENDAR            | NC-17  |
...
1000 rows in set (0.00 sec)

In this example, we used CASE to convert a film ranting to a number as a index. Then, the ORDER BY clause sort the rows by this number.

You might think that the CASE clause is very complicated, especially there are a lot of value in the list. Alternatively, you can use the FIELD() function.

FIELD() function

For the example above CASE statements, we can use the following FIELD() instead.

SELECT *
FROM film
ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');

The output is exactly the same as the CASE example.

In this example, we use FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17') as an expression in the ORDER BY clause. The FIELD(value, value1, value2, ...) function returns the position of value in the list value1, value2, ....

ORDER BY and NULL

In ascending sorting in MySQL, NULL values always ​​appear before non-NULL values.

Our example below uses the following temporary data as a demonstration:

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT 0 AS v
UNION ALL
SELECT 1 AS v;

+------+
| v    |
+------+
| A    |
| B    |
| NULL |
| 0    |
| 1    |
+------+

When we sort the values in ascending order, NULL ​​appears before non-NULL values, as follows:

SELECT *
FROM (
    SELECT 'A' AS v
    UNION ALL
    SELECT 'B' AS v
    UNION ALL
    SELECT NULL AS v
    UNION ALL
    SELECT 0 AS v
    UNION ALL
    SELECT 1 AS v
) t
ORDER BY v;

+------+
| v    |
+------+
| NULL |
| 0    |
| 1    |
| A    |
| B    |
+------+

When we sort the values in descending order, NULL ​​appears after non-NULL values, as follows:

SELECT *
FROM (
    SELECT 'A' AS v
    UNION ALL
    SELECT 'B' AS v
    UNION ALL
    SELECT NULL AS v
    UNION ALL
    SELECT 0 AS v
    UNION ALL
    SELECT 1 AS v
) t
ORDER BY v DESC;

+------+
| v    |
+------+
| B    |
| A    |
| 1    |
| 0    |
| NULL |
+------+

Conclusion

In this article, you learned how to use MySQL ORDER BY clause sort the rows in ascending and descending order, and how to implement custom sorting. The main points of the ORDER BY clause are as follows:

  • You can use one or more columns in the ORDER BY clause.
  • To sort the rows in ascending order, you can use ASC. To sort the rows in descending order, you can use DESC.
  • To sort the rows in custom order, you can use the FIELD() function or CASE statement.
  • In ascending order, NULL values are before the non-NULL values, and in descending order, NULL values are after the non-NULL values.