ORDER BY Clause

Use the ORDER BY clause to sort query results by one or more columns or expressions.

Syntax

ORDER BY <expression> [ASC|DESC] [NULLS {FIRST|LAST}] [, ...]
ORDER BY ALL [ASC|DESC] [NULLS {FIRST|LAST}]

Choose Values to Sort By

For each <expression>, use one of these values.

• Output column name: Sorts by a column in the query result.
• Output column position: Sorts by the column's left-to-right position in the result. Use a position when the column doesn't have a unique name.
• Expression based on input columns: Sorts by a calculated value. The expression can use a column that isn't in the SELECT list.

If a name matches both an output column and an input column, ORDER BY uses the output column.

When you sort by multiple expressions, Data 360 SQL evaluates them from left to right. If two rows have the same value for the first expression, the next expression determines their order. Rows that match on every expression are returned in an unspecified order.

Set Sort Direction and Null Placement

You can add sort options after each expression. An option applies only to its preceding expression. For example, ORDER BY x, y DESC sorts x in ascending order and y in descending order. It isn't the same as ORDER BY x DESC, y DESC.

Use NULLS FIRST or NULLS LAST to override the default null placement for a sort direction.

Collation-Aware Sorting of Character Strings

Data 360 SQL sorts character strings according to the collation for the column. To use a different collation, add COLLATE to the sort expression. For example, ORDER BY mycolumn COLLATE "en_US".

Sort on All Output Columns

Use ORDER BY ALL to sort by every column in the query result without listing each column. Data 360 SQL sorts by the first result column, then uses the next column as a tie-breaker. It continues from left to right through all result columns.

Restrictions:

• You can't list other sort expressions with ORDER BY ALL.
• You can't use ORDER BY ALL with SELECT DISTINCT ON.
• You can't use ORDER BY ALL in a window function, aggregate function, or table-valued function. ORDER BY ALL is supported only in a query's top-level ORDER BY clause.

Examples

Sort by an Input Column

This query sorts by an input column that isn’t in the SELECT list.

SELECT name FROM distributors ORDER BY code;

Sort by Every Output Column

This query sorts first by name and then by code because those columns appear in that order in the SELECT list.

SELECT name, code FROM distributors ORDER BY ALL;

SELECT name, code FROM distributors ORDER BY name, code;

Ascending Order

By default, ascending order sorts from lowest to highest and places null values last.

SELECT x
FROM (VALUES (1), (NULL), (2)) AS r_nulls(x)
ORDER BY x;

Output:

 x
----
  1
  2
NULL

Put Null Values First

NULLS FIRST places null values before all non-null values.

SELECT x
FROM (VALUES (1), (NULL), (2)) AS r_nulls(x)
ORDER BY x NULLS FIRST;

Output:

 x
----
NULL
  1
  2

Descending Order

By default, descending order sorts from highest to lowest and places null values first.

SELECT x
FROM (VALUES (1), (NULL), (2)) AS r_nulls(x)
ORDER BY x DESC;

Output:

 x
----
NULL
  2
  1

Put Null Values Last

Add NULLS LAST to place null values after all other values when sorting in descending order.

SELECT x
FROM (VALUES (1), (NULL), (2)) AS r_nulls(x)
ORDER BY x DESC NULLS LAST;

Output:

 x
----
  2
  1
NULL

Use the Default Sort Order

ORDER BY ALL applies the same sort direction and null placement to every result column. This query uses the default ascending order and places null values last in each column.

SELECT x, y
FROM (VALUES (1, NULL), (NULL, 2), (1, 1), (NULL, NULL)) AS oba_nulls(x, y)
ORDER BY ALL;

Output:

  x   |  y
------+------
    1 |    1
    1 | NULL
 NULL |    2
 NULL | NULL

Sort Every Column in Descending Order

This query sorts every result column in descending order. By default, it places null values first in each column.

SELECT x, y
FROM (VALUES (1, NULL), (NULL, 2), (1, 1), (NULL, NULL)) AS oba_nulls(x, y)
ORDER BY ALL DESC;

Output:

  x   |  y
------+------
 NULL | NULL
 NULL |    2
    1 | NULL
    1 |    1

Override Null Placement

To change where null values appear, add NULLS FIRST or NULLS LAST. This query sorts every column in ascending order and places null values first.

SELECT x, y
FROM (VALUES (1, NULL), (NULL, 2), (1, 1), (NULL, NULL)) AS oba_nulls(x, y)
ORDER BY ALL NULLS FIRST;

Output:

  x   |  y
------+------
 NULL | NULL
 NULL |    2
    1 | NULL
    1 |    1