# `ORDER BY` Clause

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

## Syntax

```sql
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`.

| Option        | Result                                                                            |
| ------------- | --------------------------------------------------------------------------------- |
| `ASC`         | Sorts in ascending order and places null values last. This option is the default. |
| `DESC`        | Sorts in descending order and places null values first.                           |
| `NULLS FIRST` | Places null values before all non-null values.                                    |
| `NULLS LAST`  | Places null values after all non-null values.                                     |

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.

```sql
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.

```sql
SELECT name, code FROM distributors ORDER BY ALL;
```

```sql
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.

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

Output:

```sql
 x
----
  1
  2
NULL
```

### Put Null Values First

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

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

Output:

```sql
 x
----
NULL
  1
  2
```

### Descending Order

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

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

Output:

```sql
 x
----
NULL
  2
  1
```

### Put Null Values Last

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

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

Output:

```sql
 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.

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

Output:

```sql
  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.

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

Output:

```sql
  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.

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

Output:

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