ORDER BY Clause
The ORDER BY
clause sorts the result rows by the specified expressions. If two rows are equal according to the leftmost expression, they’re compared according to the next expression. If they’re equal according to all specified expressions, they’re returned in an implementation-dependent order. Ordering options apply to the expression that they follow. For example, ORDER BY x, y DESC
isn’t the same as ORDER BY x DESC, y DESC
.
Each <expression>
can be the name or ordinal number of an output column, or it can be an expression formed from input-column values. The ordinal number refers to the left-to-right position of the output column. You can use this number to define an ordering for a column without a unique name.
You can use expressions in the ORDER BY
clause, including columns that aren’t in the SELECT
output list. This is expression is an example of a SELECT
statement with an expression in the ORDER BY
clause.
An ORDER BY
clause applied to the result of a UNION
, INTERSECT
, or EXCEPT
clause must specify an output column name or number.
If an ORDER BY
expression matches both an output and an input column name, ORDER BY
interprets it as the output column name.
You can add the key word ASC
(ascending) or DESC
(descending) after any expression in the ORDER BY
clause. ORDER BY
uses ASC
by default.
If you specify NULLS LAST
, null values are sorted after all non-null values. If you specify NULLS FIRST
, null values are sorted before all non-null values. By default, ORDER BY
uses NULLS LAST
if ASC
is used and NULLS FIRST
if DESC
is used.
Character-string data is sorted according to the collation that applies to the column being sorted. You can override this sorting by including a COLLATE
clause in the <expression>
. For example, ORDER BY mycolumn COLLATE "en_US"
.