SQL ORDER BY

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],
...;

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.

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

ORDER BY column1 ASC;

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

ORDER BY column1 DESC;

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

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;

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:

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