# Ordering Query Results

The `ORDER BY` clause controls the order in which rows are returned or processed. It can be used in any selection query, including as operand of `INSERT` or `UPSERT`, as well as with `DELETE` and `UPDATE` statements.

## Synopsis

## Parameters

The `ORDER BY` clause takes a comma-separated list of ordering specifications. Each ordering specification is composed of a column selection followed optionally by the keyword `ASC` or `DESC`.

Each column selection can take one of the following forms:

- A simple column selection, determined as follows:
  1. The name of a column label configured with `AS` earlier in the `SELECT` clause. This uses the value computed by the `SELECT` clause as the sorting key.
  2. A positive integer number, designating one of the columns in the data source, either the `FROM` clause of the `SELECT` clause where it happens or the table being written to by `DELETE` or `UPDATE`. This uses the corresponding input value from the data source to use as the sorting key.
  3. An arbitrary scalar expression. This uses the result of evaluating that expression as the sorting key.
- The notation `PRIMARY KEY <table_name>`. This uses the primary key column(s) of the given table as sorting key. This table must be part of the data source.
- The notation `INDEX <table_name>@<index_name>`. This uses the columns indexed by the given index as sorting key. This table must be part of the data source.

The optional keyword `ASC` after a column selection indicates to use the sorting key as-is, and thus is meaningless.

The optional keyword `DESC` inverts the direction of the column(s) selected by the selection that immediately precedes.

## Order preservation

In general, the order of the intermediate results of a query is not guaranteed, even if `ORDER BY` is specified. In other words, the `ORDER BY` clause is only effective at the top-level statement. For example, it is ignored by the query planner when present in a sub-query in a `FROM` clause as follows:

    >  SELECT * FROM a, b ORDER BY a.x;                 -- valid, effective
    >  SELECT * FROM (SELECT * FROM a ORDER BY a.x), b; -- ignored, ineffective

However, when combining queries together with sub-queries, some combinations will make the `ORDER BY` clause in a sub-query significant:

1. The ordering of the operand of a `WITH ORDINALITY` clause (within the `FROM` operand of a `SELECT` clause) is preserved, to control the numbering of the rows.
2. The ordering of the operand of a stand-alone `LIMIT` or `OFFSET` clause (within a `FROM` operand of a `SELECT` clause) is preserved, to determine which rows are kept in the result.
3. The ordering of the data source for an `INSERT` statement or an `UPSERT` statement that also uses `LIMIT` is preserved, to determine which rows are processed.
4. The ordering indicated for an `UPDATE` or `DELETE` statement that also uses `LIMIT` is used to determine which rows are processed. (This is a CockroachDB extension.)
5. The ordering of a sub-query used in a scalar expression is preserved.

For example, using `WITH ORDINALITY`:

    > SELECT * FROM (SELECT * FROM a ORDER BY a.x) WITH ORDINALITY;
      -- ensures that the rows are numbered in the order of column a.x.

For example, using a stand-alone `LIMIT` clause in `FROM`:

    > SELECT * FROM a, ((SELECT * FROM b ORDER BY b.x) LIMIT 1);
      -- ensures that only the first row of b in the order of column b.x
      -- is used in the cross join.

For example, using `LIMIT` in `INSERT`:

    > INSERT INTO a (SELECT * FROM b ORDER BY b.x) LIMIT 1;
      -- ensures that only the first row of b in the order of column b.x
      -- is inserted into a.

For example, using a sub-query in scalar context:

    > SELECT ARRAY(SELECT a.x FROM a ORDER BY a.x);
      -- ensures that the array is constructed using the values of a.x in sorted order.
    > SELECT (1, 2, 3) = (SELECT a.x FROM a ORDER BY a.x);
      -- ensures that the values on the right-hand side are compared in the order of column a.x.

## Ordering of rows without `ORDER BY`

Without `ORDER BY`, rows are processed or returned in a non-deterministic order. "Non-deterministic" means that the actual order can depend on the logical plan, the order of data on disk, the topology of the CockroachDB cluster, and is generally variable over time.

## Sorting using simple column selections

Considering the following table:

    > CREATE TABLE a(a INT);
    > INSERT INTO a VALUES (1), (3), (2);

The following statements are equivalent:

    > SELECT a AS b FROM a ORDER BY b; -- first form: refers to an AS alias.
    > SELECT a      FROM a ORDER BY 1; -- second form: refers to a column position.
    > SELECT a      FROM a ORDER BY a; -- third form: refers to a column in the data source.

    +---------+
    | a       |
    +---------+
    |       1 |
    |       2 |
    |       3 |
    +---------+
    (3 rows)

Note that the order of the rules matter. If there is ambiguity, the `AS` aliases take priority over the data source columns, for example:

    > CREATE TABLE ab(a INT, b INT);
    > SELECT a AS b, b AS c FROM ab ORDER BY b; -- orders by column a, renamed to b
    > SELECT a,      b      FROM ab ORDER BY b; -- orders by column b

It is also possible to sort using an arbitrary scalar expression computed for each row, for example:

    > SELECT a, b FROM ab ORDER BY a + b; -- orders by the result of computing a+b.

## Sorting using multiple columns

When more than one ordering specification is given, the later specifications are used to order rows that are equal over the earlier specifications, for example:

    > CREATE TABLE ab(a INT, b INT);
    > SELECT a, b FROM ab ORDER BY b, a;

This sorts the results by column `b`, and then if there are multiple rows that have the same value in column `b`, it will then order these rows by column `a`.

## Inverting the sort order

The keyword `DESC` ("descending") can be added after an ordering specification to invert its order. This can be specified separately for each specification, for example:

    > CREATE TABLE ab(a INT, b INT);
    > SELECT a, b FROM ab ORDER BY b DESC, a; -- sorts on b descending, then a ascending.

## Sorting in primary key order

The `ORDER BY PRIMARY KEY` notation guarantees that the results are presented in primary key order.

The particular advantage is that for queries using the primary index, this guarantees the order while also guaranteeing there will not be an additional sorting computation to achieve it, for example:

    > CREATE TABLE kv(k INT PRIMARY KEY, v INT);
    > SELECT k, v FROM kv ORDER BY PRIMARY KEY kv; -- guarantees ordering by column k.

If a primary key uses the keyword `DESC` already, then its meaning will be flipped (cancelled) if the `ORDER BY` clause also uses `DESC`, for example:

    > CREATE TABLE ab(a INT, b INT, PRIMARY KEY (b DESC, a ASC));
    > SELECT * FROM ab ORDER BY b DESC; -- orders by b descending, then a ascending.
                                        -- The primary index may be used to optimize.

    > SELECT * FROM ab ORDER BY PRIMARY KEY ab DESC; -- orders by b ascending, then a descending.
                                                     -- The index order is inverted.

## Sorting in index order

The `ORDER BY INDEX` notation guarantees that the results are presented in the order of a given index.

The particular advantage is that for queries using that index, this guarantees the order while also guaranteeing there will not be an additional sorting computation to achieve it, for example:

    > CREATE TABLE kv(k INT PRIMARY KEY, v INT, INDEX v_idx(v));
    > SELECT k, v FROM kv ORDER BY INDEX kv@v_idx; -- guarantees ordering by column v.

If an index uses the keyword `DESC` already, then its meaning will be flipped (cancelled) if the `ORDER BY` clause also uses `DESC`, for example:

    > CREATE TABLE ab(a INT, b INT, INDEX b_idx (b DESC, a ASC));
    > SELECT * FROM ab ORDER BY b DESC; -- orders by b descending, then a ascending.
                                        -- The index b_idx may be used to optimize.

    > SELECT * FROM ab ORDER BY INDEX ab@b_idx DESC; -- orders by b ascending, then a descending.
                                                     -- The index order is inverted.

## Processing order during aggregations

CockroachDB currently processes aggregations (e.g., `SELECT ... GROUP BY`) in non-deterministic order.

For most aggregation functions, like `MIN`, `MAX`, `COUNT`, the order does not matter anyway because the functions are commutative and produce the same result regardless. However, for the few aggregation functions that are not commutative (e.g., `array_agg()`, `json_agg()`, and `concat_agg()`), this implies the result of the aggregation will not be deterministic.

This is a known limitation that may be lifted in the future.

## See also

- Selection Queries
- Scalar Expressions
- `INSERT`
- `UPSERT`
- `DELETE`
- `UPDATE`