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