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".