Categories:

Query Syntax

ORDER BY

Specifies an ordering of the rows of the result table from a SELECT list.

Syntax

SELECT ...
FROM ...
ORDER BY orderItem [ , orderItem , ... ]
[ ... ]
Where:
orderItem ::= { <column_alias> | <position> | <expr> } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
column_alias

Column alias appearing in the query block’s SELECT list.

position

Position of an expression in the SELECT list.

expr

Any expression on tables in the current scope.

ASC | DESC

Optionally returns the values of the sort key in ascending (lowest to highest) or descending (highest to lowest) order.

Default: ASC

NULLS FIRST | LAST

Optionally specifies whether NULL values are returned before/after non-NULL values, based on the sort order (ASC or DESC).

Default: Depends on the sort order (ASC or DESC); see the usage notes below for details

Usage Notes

  • All data is sorted according to the numeric byte value of each character in the ASCII table. UTF-8 encoding is supported.

  • For numeric values, leading zeroes before the decimal point and trailing zeros (0) after the decimal point have no effect on sort order.

  • Unless specified otherwise, NULL values are considered to be higher than any non-NULL values. As a result, the ordering for NULLS depends on the sort order:

    • If the sort order is ASC, NULLS are returned last; to force NULLS to be first, use NULLS FIRST.

    • If the sort order is DESC, NULLS are returned first; to force NULLS to be last, use NULLS LAST.

Examples

Sort order example for strings:

SELECT column1
FROM VALUES ('a'), ('1'), ('B'), (null), ('2'), ('01'), ('05'), (' this'), ('this'), ('this and that'), ('&'), ('%')
ORDER BY column1;

+---------------+
| COLUMN1       |
|---------------|
|  this         |
| %             |
| &             |
| 01            |
| 05            |
| 1             |
| 2             |
| B             |
| a             |
| this          |
| this and that |
| NULL          |
+---------------+

Sort order example for numbers:

SELECT column1
FROM VALUES (3), (4), (null), (1), (2), (6), (5), (0005), (.05), (.5), (.5000)
ORDER BY column1;

+---------+
| COLUMN1 |
|---------|
|    0.05 |
|    0.50 |
|    0.50 |
|    1.00 |
|    2.00 |
|    3.00 |
|    4.00 |
|    5.00 |
|    5.00 |
|    6.00 |
|    NULL |
+---------+

Sort order example for NULL values:

SELECT column1
FROM VALUES (1), (null), (2), (null), (3)
ORDER BY column1;

+---------+
| COLUMN1 |
|---------|
|       1 |
|       2 |
|       3 |
|    NULL |
|    NULL |
+---------+

SELECT column1
FROM VALUES (1), (null), (2), (null), (3)
ORDER BY column1 NULLS FIRST;

+---------+
| COLUMN1 |
|---------|
|    NULL |
|    NULL |
|       1 |
|       2 |
|       3 |
+---------+

SELECT column1
FROM VALUES (1), (null), (2), (null), (3)
ORDER BY column1 DESC;

+---------+
| COLUMN1 |
|---------|
|    NULL |
|    NULL |
|       3 |
|       2 |
|       1 |
+---------+

SELECT column1
FROM VALUES (1), (null), (2), (null), (3)
ORDER BY column1 DESC NULLS LAST;

+---------+
| COLUMN1 |
|---------|
|       3 |
|       2 |
|       1 |
|    NULL |
|    NULL |
+---------+