ORDER BY clause contains a list of expressions, which can each be attributed with
DESC (descending) or
ASC (ascending) modifier which determine the sorting direction. If the direction is not specified,
ASC is assumed, so it's usually omitted. The sorting direction applies to a single expression, not to the entire list. Example:
ORDER BY Visits DESC, SearchPhrase
Rows that have identical values for the list of sorting expressions are output in an arbitrary order, which can also be non-deterministic (different each time).
If the ORDER BY clause is omitted, the order of the rows is also undefined, and may be non-deterministic as well.
There are two approaches to
NULL sorting order:
- By default or with the
NULLS LASTmodifier: first the values, then
- With the
NULLS FIRSTmodifier: first
NaN, then other values.
For the table
┌─x─┬────y─┐ │ 1 │ ᴺᵁᴸᴸ │ │ 2 │ 2 │ │ 1 │ nan │ │ 2 │ 2 │ │ 3 │ 4 │ │ 5 │ 6 │ │ 6 │ nan │ │ 7 │ ᴺᵁᴸᴸ │ │ 6 │ 7 │ │ 8 │ 9 │ └───┴──────┘
Run the query
SELECT * FROM t_null_nan ORDER BY y NULLS FIRST to get:
┌─x─┬────y─┐ │ 1 │ ᴺᵁᴸᴸ │ │ 7 │ ᴺᵁᴸᴸ │ │ 1 │ nan │ │ 6 │ nan │ │ 2 │ 2 │ │ 2 │ 2 │ │ 3 │ 4 │ │ 5 │ 6 │ │ 6 │ 7 │ │ 8 │ 9 │ └───┴──────┘
When floating point numbers are sorted, NaNs are separate from the other values. Regardless of the sorting order, NaNs come at the end. In other words, for ascending sorting they are placed as if they are larger than all the other numbers, while for descending sorting they are placed as if they are smaller than the rest.
For sorting by String values, you can specify collation (comparison). Example:
ORDER BY SearchPhrase COLLATE 'tr' - for sorting by keyword in ascending order, using the Turkish alphabet, case insensitive, assuming that strings are UTF-8 encoded.
COLLATE can be specified or not for each expression in ORDER BY independently. If
DESC is specified,
COLLATE is specified after it. When using
COLLATE, sorting is always case-insensitive.
We only recommend using
COLLATE for final sorting of a small number of rows, since sorting with
COLLATE is less efficient than normal sorting by bytes.
Less RAM is used if a small enough LIMIT is specified in addition to
ORDER BY. Otherwise, the amount of memory spent is proportional to the volume of data for sorting. For distributed query processing, if GROUP BY is omitted, sorting is partially done on remote servers, and the results are merged on the requestor server. This means that for distributed sorting, the volume of data to sort can be greater than the amount of memory on a single server.
If there is not enough RAM, it is possible to perform sorting in external memory (creating temporary files on a disk). Use the setting
max_bytes_before_external_sort for this purpose. If it is set to 0 (the default), external sorting is disabled. If it is enabled, when the volume of data to sort reaches the specified number of bytes, the collected data is sorted and dumped into a temporary file. After all data is read, all the sorted files are merged and the results are output. Files are written to the
/var/lib/clickhouse/tmp/ directory in the config (by default, but you can use the
tmp_path parameter to change this setting).
Running a query may use more memory than
max_bytes_before_external_sort. For this reason, this setting must have a value significantly smaller than
max_memory_usage. As an example, if your server has 128 GB of RAM and you need to run a single query, set
max_memory_usage to 100 GB, and
max_bytes_before_external_sort to 80 GB.
External sorting works much less effectively than sorting in RAM.