`ORDER BY` clause

The ORDER BY clause is an output modifier. It is logically applied at the very end of the query (just before LIMIT, if present). The ORDER BY clause sorts rows by sort criteria in ascending or descending order. In addition, in each ORDER BY clause, you can specify whether to move NULL values to the beginning or the end.

A ORDER BY clause may contain one or more comma-separated expressions. If there are no expressions, an error will be generated. Expressions can begin with either an arbitrary scalar expression (which can be a column name), a column position number (where indexing begins with 1), or the ALL keyword. Each expression may be followed by an order modifier (ASC or DESC, default is ASC), and/or a NULL order modifier (NULLS FIRST or NULLS LAST, default is NULLS LAST).

`ORDER BY ALL`

The ALL keyword specifies that the output should be sorted by each column in order from left to right. The sort direction can be changed using ORDER BY ALL ASC or ORDER BY ALL DESC and/or NULLS FIRST or NULLS LAST. Note that ALL cannot be used in combination with other expressions in a ORDER BY clause - it must be by itself.

The `NULL` value order modifier

By default, sorting is done with the ASC and NULLS LAST parameters, that is, the values are sorted in ascending order, with the NULL values placed last. This is identical to the default sort order in PostgreSQL. The default sort order can be changed using the following configuration parameters.

Use the default_null_order parameter to change the default NULL sort order to one of the following options:

NULLS_FIRST.
NULLS_LAST
NULLS_FIRST_ON_ASC_LAST_ON_DESC
NULLS_LAST_ON_ASC_FASC_FIRST_ON_DESC:

For example:

SET default_null_order = 'NULLS_FIRST';

Use the default_order parameter to change the default sort direction to one of the following options:

DESC.
ASC

For example:

SET default_order = 'DESC';

Collations (matching schemes)

Text is sorted by default using binary comparison collation. This means that values are sorted by their binary representations in UTF-8. While this works well for ASCII-text (for example, for data in English), the sort order may not be correct for other languages. Collations are provided for this purpose.

Syntax

SELECT ...
FROM ...
ORDER BY orderItem [ , orderItem , ... ]
[ ... ]

Where:

orderItem ::= { <column_alias> | <position> | <expr> }
    [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ]

Parameters

<column_alias>
The alias of the column specified in the SELECT list.

<position>
The position of the expression in the SELECT list.

<expr>
Any expression defined on tables in the current scope.

{ ASC | DESC }
Optionally returns the sort key values in ascending (smallest to largest) or descending (largest to smallest) order.
Default: ASC

NULLS { FIRST | LAST }
Optionally specifies whether NULL values are returned before/after values other than NULL, depending on the sort order (ASC or DESC).
Default: depends on the sort order (ASC or DESC), see The NULL value order modifier.

Examples

Output the days of the week, ordered by their name, using the default sort order and the standard order for NULL values:

SELECT *
FROM weekdays
ORDER BY name;

+--------+-----------+
| number |    name   |
+--------+-----------+
| 5      | Friday    |
+--------+-----------+
| 1      | Monday    |
+--------+-----------+
| 6      | Saturday  |
+--------+-----------+
| 7      | Sunday    |
+--------+-----------+
| 4      | Thursday  |
+--------+-----------+
| 2      | Tuesday   |
+--------+-----------+
| 3      | Wednesday |
+--------+-----------+
| 8      | null      |
+--------+-----------+

Let’s output the days of the week ordered by their name in descending order with NULL values at the beginning:

SELECT *
FROM weekdays
ORDER BY name DESC NULLS FIRST;

+--------+-----------+
| number |    name   |
+--------+-----------+
| 8      | null      |
+--------+-----------+
| 3      | Wednesday |
+--------+-----------+
| 2      | Tuesday   |
+--------+-----------+
| 4      | Thursday  |
+--------+-----------+
| 7      | Sunday    |
+--------+-----------+
| 6      | Saturday  |
+--------+-----------+
| 1      | Monday    |
+--------+-----------+
| 5      | Friday    |
+--------+-----------+

Now let’s consider a situation when our table has the days of the week numbered starting from Sunday (as is done in some calendar systems). To bring it back to the usual form, we order the days of the week first by type (weekend or not) and then by number:

SELECT *
FROM weekdays
ORDER BY weekend, number;

+--------+-----------+---------+
| number |    name   | weekend |
+--------+-----------+---------+
| 2      | Monday    | false   |
+--------+-----------+---------+
| 3      | Tuesday   | false   |
+--------+-----------+---------+
| 4      | Wednesday | false   |
+--------+-----------+---------+
| 5      | Thursday  | false   |
+--------+-----------+---------+
| 6      | Friday    | false   |
+--------+-----------+---------+
| 1      | Sunday    | true    |
+--------+-----------+---------+
| 7      | Saturday  | true    |
+--------+-----------+---------+

Let’s show the difference in sorting using different collations. Let’s take the names of two Finnish cities in their Swedish and Finnish variants and sort them by their Swedish names, using the collation rules for English first:

SELECT *
FROM finnish_cities
ORDER BY swed_name COLLATE EN

+-------------+-----------+
|  swed_name  |  fin_name |
+-------------+-----------+
| Åbo         | Turku     |
+-------------+-----------+
| Helsingfors | Helsinki  |
+-------------+-----------+

Now let’s do the same thing, but use the collation rules for Swedish:

SELECT *
FROM finnish_cities
ORDER BY swed_name COLLATE SV

+-------------+-----------+
|  swed_name  |  fin_name |
+-------------+-----------+
| Helsingfors | Helsinki  |
+-------------+-----------+
| Åbo         | Turku     |
+-------------+-----------+

In the Swedish alphabet, the letter å comes at the end, while in the English collation rules it comes at the beginning. Hence we get the difference in the sorting order.

ORDER BY clause

ORDER BY ALL

The NULL value order modifier