- Categories:
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
orDESC
).Default: Depends on the sort order (
ASC
orDESC
); 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, useNULLS FIRST
.If the sort order is
DESC
, NULLS are returned first; to force NULLS to be last, useNULLS 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 | +---------+