โน๏ธ Skipped - page is already crawled
| Filter | Status | Condition | Details |
|---|---|---|---|
| HTTP status | PASS | download_http_code = 200 | HTTP 200 |
| Age cutoff | PASS | download_stamp > now() - 6 MONTH | 0 months ago |
| History drop | PASS | isNull(history_drop_reason) | No drop reason |
| Spam/ban | PASS | fh_dont_index != 1 AND ml_spam_score = 0 | ml_spam_score=0 |
| Canonical | PASS | meta_canonical IS NULL OR = '' OR = src_unparsed | Not set |
| Property | Value |
|---|---|
| URL | https://clickhouse.com/docs/sql-reference/statements/select/order-by |
| Last Crawled | 2026-04-13 23:02:31 (11 hours ago) |
| First Indexed | 2025-02-20 23:23:45 (1 year ago) |
| HTTP Status Code | 200 |
| Meta Title | ORDER BY Clause | ClickHouse Docs |
| Meta Description | Documentation for ORDER BY Clause |
| Meta Canonical | null |
| Boilerpipe Text | The
ORDER BY
clause contains
a list of expressions, e.g.
ORDER BY visits, search_phrase
,
a list of numbers referring to columns in the
SELECT
clause, e.g.
ORDER BY 2, 1
, or
ALL
which means all columns of the
SELECT
clause, e.g.
ORDER BY ALL
.
To disable sorting by column numbers, set setting
enable_positional_arguments
= 0.
To disable sorting by
ALL
, set setting
enable_order_by_all
= 0.
The
ORDER BY
clause can be attributed by a
DESC
(descending) or
ASC
(ascending) modifier which determines the sorting direction.
Unless an explicit sort order is specified,
ASC
is used by default.
The sorting direction applies to a single expression, not to the entire list, e.g.
ORDER BY Visits DESC, SearchPhrase
.
Also, sorting is performed case-sensitively.
Rows with identical values for a sort expressions are returned in an arbitrary and non-deterministic order.
If the
ORDER BY
clause is omitted in a
SELECT
statement, the row order is also arbitrary and non-deterministic.
Sorting of Special Values
โ
There are two approaches to
NaN
and
NULL
sorting order:
By default or with the
NULLS LAST
modifier: first the values, then
NaN
, then
NULL
.
With the
NULLS FIRST
modifier: first
NULL
, then
NaN
, then other values.
Example
โ
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.
Collation Support
โ
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
ASC
or
DESC
is specified,
COLLATE
is specified after it. When using
COLLATE
, sorting is always case-insensitive.
Collate is supported in
LowCardinality
,
Nullable
,
Array
and
Tuple
.
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.
Collation Examples
โ
Example only with
String
values:
Input table:
โโxโโฌโsโโโโโ
โ 1 โ bca โ
โ 2 โ ABC โ
โ 3 โ 123a โ
โ 4 โ abc โ
โ 5 โ BCA โ
โโโโโดโโโโโโโ
Query:
SELECT
*
FROM
collate_test
ORDER
BY
s
ASC
COLLATE
'en'
;
Result:
โโxโโฌโsโโโโโ
โ 3 โ 123a โ
โ 4 โ abc โ
โ 2 โ ABC โ
โ 1 โ bca โ
โ 5 โ BCA โ
โโโโโดโโโโโโโ
Example with
Nullable
:
Input table:
โโxโโฌโsโโโโโ
โ 1 โ bca โ
โ 2 โ แดบแตแดธแดธ โ
โ 3 โ ABC โ
โ 4 โ 123a โ
โ 5 โ abc โ
โ 6 โ แดบแตแดธแดธ โ
โ 7 โ BCA โ
โโโโโดโโโโโโโ
Query:
SELECT
*
FROM
collate_test
ORDER
BY
s
ASC
COLLATE
'en'
;
Result:
โโxโโฌโsโโโโโ
โ 4 โ 123a โ
โ 5 โ abc โ
โ 3 โ ABC โ
โ 1 โ bca โ
โ 7 โ BCA โ
โ 6 โ แดบแตแดธแดธ โ
โ 2 โ แดบแตแดธแดธ โ
โโโโโดโโโโโโโ
Example with
Array
:
Input table:
โโxโโฌโsโโโโโโโโโโโโโโ
โ 1 โ ['Z'] โ
โ 2 โ ['z'] โ
โ 3 โ ['a'] โ
โ 4 โ ['A'] โ
โ 5 โ ['z','a'] โ
โ 6 โ ['z','a','a'] โ
โ 7 โ [''] โ
โโโโโดโโโโโโโโโโโโโโโโ
Query:
SELECT
*
FROM
collate_test
ORDER
BY
s
ASC
COLLATE
'en'
;
Result:
โโxโโฌโsโโโโโโโโโโโโโโ
โ 7 โ [''] โ
โ 3 โ ['a'] โ
โ 4 โ ['A'] โ
โ 2 โ ['z'] โ
โ 5 โ ['z','a'] โ
โ 6 โ ['z','a','a'] โ
โ 1 โ ['Z'] โ
โโโโโดโโโโโโโโโโโโโโโโ
Example with
LowCardinality
string:
Input table:
โโxโโฌโsโโโโ
โ 1 โ Z โ
โ 2 โ z โ
โ 3 โ a โ
โ 4 โ A โ
โ 5 โ za โ
โ 6 โ zaa โ
โ 7 โ โ
โโโโโดโโโโโโ
Query:
SELECT
*
FROM
collate_test
ORDER
BY
s
ASC
COLLATE
'en'
;
Result:
โโxโโฌโsโโโโ
โ 7 โ โ
โ 3 โ a โ
โ 4 โ A โ
โ 2 โ z โ
โ 1 โ Z โ
โ 5 โ za โ
โ 6 โ zaa โ
โโโโโดโโโโโโ
Example with
Tuple
:
โโxโโฌโsโโโโโโโโ
โ 1 โ (1,'Z') โ
โ 2 โ (1,'z') โ
โ 3 โ (1,'a') โ
โ 4 โ (2,'z') โ
โ 5 โ (1,'A') โ
โ 6 โ (2,'Z') โ
โ 7 โ (2,'A') โ
โโโโโดโโโโโโโโโโ
Query:
SELECT
*
FROM
collate_test
ORDER
BY
s
ASC
COLLATE
'en'
;
Result:
โโxโโฌโsโโโโโโโโ
โ 3 โ (1,'a') โ
โ 5 โ (1,'A') โ
โ 2 โ (1,'z') โ
โ 1 โ (1,'Z') โ
โ 7 โ (2,'A') โ
โ 4 โ (2,'z') โ
โ 6 โ (2,'Z') โ
โโโโโดโโโโโโโโโโ
Implementation Details
โ
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). You can also use spilling to disk only if query exceeds memory limits, i.e.
max_bytes_ratio_before_external_sort=0.6
will enable spilling to disk only once the query hits
60%
memory limit (user/sever).
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.
Optimization of Data Reading
โ
If
ORDER BY
expression has a prefix that coincides with the table sorting key, you can optimize the query by using the
optimize_read_in_order
setting.
When the
optimize_read_in_order
setting is enabled, the ClickHouse server uses the table index and reads the data in order of the
ORDER BY
key. This allows to avoid reading all data in case of specified
LIMIT
. So queries on big data with small limit are processed faster.
Optimization works with both
ASC
and
DESC
and does not work together with
GROUP BY
clause and
FINAL
modifier.
When the
optimize_read_in_order
setting is disabled, the ClickHouse server does not use the table index while processing
SELECT
queries.
Consider disabling
optimize_read_in_order
manually, when running queries that have
ORDER BY
clause, large
LIMIT
and
WHERE
condition that requires to read huge amount of records before queried data is found.
Optimization is supported in the following table engines:
MergeTree
(including
materialized views
),
Merge
,
Buffer
In
MaterializedView
-engine tables the optimization works with views like
SELECT ... FROM merge_tree_table ORDER BY pk
. But it is not supported in the queries like
SELECT ... FROM view ORDER BY pk
if the view query does not have the
ORDER BY
clause.
ORDER BY Expr WITH FILL Modifier
โ
This modifier also can be combined with
LIMIT ... WITH TIES modifier
.
WITH FILL
modifier can be set after
ORDER BY expr
with optional
FROM expr
,
TO expr
and
STEP expr
parameters.
All missed values of
expr
column will be filled sequentially and other columns will be filled as defaults.
To fill multiple columns, add
WITH FILL
modifier with optional parameters after each field name in
ORDER BY
section.
ORDER
BY
expr
[
WITH
FILL
]
[
FROM
const_expr
]
[
TO
const_expr
]
[
STEP const_numeric_expr
]
[
STALENESS const_numeric_expr
]
,
.
.
.
exprN
[
WITH
FILL
]
[
FROM
expr
]
[
TO
expr
]
[
STEP numeric_expr
]
[
STALENESS numeric_expr
]
[
INTERPOLATE
[
(
col
[
AS
expr
]
,
.
.
.
colN
[
AS
exprN
]
)
]
]
WITH FILL
can be applied for fields with Numeric (all kinds of float, decimal, int) or Date/DateTime types. When applied for
String
fields, missed values are filled with empty strings.
When
FROM const_expr
not defined sequence of filling use minimal
expr
field value from
ORDER BY
.
When
TO const_expr
not defined sequence of filling use maximum
expr
field value from
ORDER BY
.
When
STEP const_numeric_expr
defined then
const_numeric_expr
interprets
as is
for numeric types, as
days
for Date type, as
seconds
for DateTime type. It also supports
INTERVAL
data type representing time and date intervals.
When
STEP const_numeric_expr
omitted then sequence of filling use
1.0
for numeric type,
1 day
for Date type and
1 second
for DateTime type.
When
STALENESS const_numeric_expr
is defined, the query will generate rows until the difference from the previous row in the original data exceeds
const_numeric_expr
.
INTERPOLATE
can be applied to columns not participating in
ORDER BY WITH FILL
. Such columns are filled based on previous fields values by applying
expr
. If
expr
is not present will repeat previous value. Omitted list will result in including all allowed columns.
Example of a query without
WITH FILL
:
SELECT
n
,
source
FROM
(
SELECT
toFloat32
(
number
%
10
)
AS
n
,
'original'
AS
source
FROM
numbers
(
10
)
WHERE
number
%
3
=
1
)
ORDER
BY
n
;
Result:
โโnโโฌโsourceโโโโ
โ 1 โ original โ
โ 4 โ original โ
โ 7 โ original โ
โโโโโดโโโโโโโโโโโ
Same query after applying
WITH FILL
modifier:
SELECT
n
,
source
FROM
(
SELECT
toFloat32
(
number
%
10
)
AS
n
,
'original'
AS
source
FROM
numbers
(
10
)
WHERE
number
%
3
=
1
)
ORDER
BY
n
WITH
FILL
FROM
0
TO
5.51
STEP
0.5
;
Result:
โโโโnโโฌโsourceโโโโ
โ 0 โ โ
โ 0.5 โ โ
โ 1 โ original โ
โ 1.5 โ โ
โ 2 โ โ
โ 2.5 โ โ
โ 3 โ โ
โ 3.5 โ โ
โ 4 โ original โ
โ 4.5 โ โ
โ 5 โ โ
โ 5.5 โ โ
โ 7 โ original โ
โโโโโโโดโโโโโโโโโโโ
For the case with multiple fields
ORDER BY field2 WITH FILL, field1 WITH FILL
order of filling will follow the order of fields in the
ORDER BY
clause.
Example:
SELECT
toDate
(
(
number
*
10
)
*
86400
)
AS
d1
,
toDate
(
number
*
86400
)
AS
d2
,
'original'
AS
source
FROM
numbers
(
10
)
WHERE
(
number
%
3
)
=
1
ORDER
BY
d2
WITH
FILL
,
d1
WITH
FILL STEP
5
;
Result:
โโโโd1โโโโโโโโฌโโโd2โโโโโโโโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-01 โ 1970-01-03 โ โ
โ 1970-01-01 โ 1970-01-04 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-01-01 โ 1970-01-06 โ โ
โ 1970-01-01 โ 1970-01-07 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
Field
d1
does not fill in and use the default value cause we do not have repeated values for
d2
value, and the sequence for
d1
can't be properly calculated.
The following query with the changed field in
ORDER BY
:
SELECT
toDate
(
(
number
*
10
)
*
86400
)
AS
d1
,
toDate
(
number
*
86400
)
AS
d2
,
'original'
AS
source
FROM
numbers
(
10
)
WHERE
(
number
%
3
)
=
1
ORDER
BY
d1
WITH
FILL STEP
5
,
d2
WITH
FILL
;
Result:
โโโโd1โโโโโโโโฌโโโd2โโโโโโโโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-16 โ 1970-01-01 โ โ
โ 1970-01-21 โ 1970-01-01 โ โ
โ 1970-01-26 โ 1970-01-01 โ โ
โ 1970-01-31 โ 1970-01-01 โ โ
โ 1970-02-05 โ 1970-01-01 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-02-15 โ 1970-01-01 โ โ
โ 1970-02-20 โ 1970-01-01 โ โ
โ 1970-02-25 โ 1970-01-01 โ โ
โ 1970-03-02 โ 1970-01-01 โ โ
โ 1970-03-07 โ 1970-01-01 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
The following query uses the
INTERVAL
data type of 1 day for each data filled on column
d1
:
SELECT
toDate
(
(
number
*
10
)
*
86400
)
AS
d1
,
toDate
(
number
*
86400
)
AS
d2
,
'original'
AS
source
FROM
numbers
(
10
)
WHERE
(
number
%
3
)
=
1
ORDER
BY
d1
WITH
FILL STEP
INTERVAL
1
DAY
,
d2
WITH
FILL
;
Result:
โโโโโโโโโโd1โโฌโโโโโโโโโd2โโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-12 โ 1970-01-01 โ โ
โ 1970-01-13 โ 1970-01-01 โ โ
โ 1970-01-14 โ 1970-01-01 โ โ
โ 1970-01-15 โ 1970-01-01 โ โ
โ 1970-01-16 โ 1970-01-01 โ โ
โ 1970-01-17 โ 1970-01-01 โ โ
โ 1970-01-18 โ 1970-01-01 โ โ
โ 1970-01-19 โ 1970-01-01 โ โ
โ 1970-01-20 โ 1970-01-01 โ โ
โ 1970-01-21 โ 1970-01-01 โ โ
โ 1970-01-22 โ 1970-01-01 โ โ
โ 1970-01-23 โ 1970-01-01 โ โ
โ 1970-01-24 โ 1970-01-01 โ โ
โ 1970-01-25 โ 1970-01-01 โ โ
โ 1970-01-26 โ 1970-01-01 โ โ
โ 1970-01-27 โ 1970-01-01 โ โ
โ 1970-01-28 โ 1970-01-01 โ โ
โ 1970-01-29 โ 1970-01-01 โ โ
โ 1970-01-30 โ 1970-01-01 โ โ
โ 1970-01-31 โ 1970-01-01 โ โ
โ 1970-02-01 โ 1970-01-01 โ โ
โ 1970-02-02 โ 1970-01-01 โ โ
โ 1970-02-03 โ 1970-01-01 โ โ
โ 1970-02-04 โ 1970-01-01 โ โ
โ 1970-02-05 โ 1970-01-01 โ โ
โ 1970-02-06 โ 1970-01-01 โ โ
โ 1970-02-07 โ 1970-01-01 โ โ
โ 1970-02-08 โ 1970-01-01 โ โ
โ 1970-02-09 โ 1970-01-01 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-02-11 โ 1970-01-01 โ โ
โ 1970-02-12 โ 1970-01-01 โ โ
โ 1970-02-13 โ 1970-01-01 โ โ
โ 1970-02-14 โ 1970-01-01 โ โ
โ 1970-02-15 โ 1970-01-01 โ โ
โ 1970-02-16 โ 1970-01-01 โ โ
โ 1970-02-17 โ 1970-01-01 โ โ
โ 1970-02-18 โ 1970-01-01 โ โ
โ 1970-02-19 โ 1970-01-01 โ โ
โ 1970-02-20 โ 1970-01-01 โ โ
โ 1970-02-21 โ 1970-01-01 โ โ
โ 1970-02-22 โ 1970-01-01 โ โ
โ 1970-02-23 โ 1970-01-01 โ โ
โ 1970-02-24 โ 1970-01-01 โ โ
โ 1970-02-25 โ 1970-01-01 โ โ
โ 1970-02-26 โ 1970-01-01 โ โ
โ 1970-02-27 โ 1970-01-01 โ โ
โ 1970-02-28 โ 1970-01-01 โ โ
โ 1970-03-01 โ 1970-01-01 โ โ
โ 1970-03-02 โ 1970-01-01 โ โ
โ 1970-03-03 โ 1970-01-01 โ โ
โ 1970-03-04 โ 1970-01-01 โ โ
โ 1970-03-05 โ 1970-01-01 โ โ
โ 1970-03-06 โ 1970-01-01 โ โ
โ 1970-03-07 โ 1970-01-01 โ โ
โ 1970-03-08 โ 1970-01-01 โ โ
โ 1970-03-09 โ 1970-01-01 โ โ
โ 1970-03-10 โ 1970-01-01 โ โ
โ 1970-03-11 โ 1970-01-01 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
Example of a query without
STALENESS
:
SELECT
number
AS
key
,
5
*
number
value
,
'original'
AS
source
FROM
numbers
(
16
)
WHERE
key
%
5
=
=
0
ORDER
BY
key
WITH
FILL
;
Result:
โโkeyโโฌโvalueโโฌโsourceโโโโ
1. โ 0 โ 0 โ original โ
2. โ 1 โ 0 โ โ
3. โ 2 โ 0 โ โ
4. โ 3 โ 0 โ โ
5. โ 4 โ 0 โ โ
6. โ 5 โ 25 โ original โ
7. โ 6 โ 0 โ โ
8. โ 7 โ 0 โ โ
9. โ 8 โ 0 โ โ
10. โ 9 โ 0 โ โ
11. โ 10 โ 50 โ original โ
12. โ 11 โ 0 โ โ
13. โ 12 โ 0 โ โ
14. โ 13 โ 0 โ โ
15. โ 14 โ 0 โ โ
16. โ 15 โ 75 โ original โ
โโโโโโโดโโโโโโโโดโโโโโโโโโโโ
Same query after applying
STALENESS 3
:
SELECT
number
AS
key
,
5
*
number
value
,
'original'
AS
source
FROM
numbers
(
16
)
WHERE
key
%
5
=
=
0
ORDER
BY
key
WITH
FILL STALENESS
3
;
Result:
โโkeyโโฌโvalueโโฌโsourceโโโโ
1. โ 0 โ 0 โ original โ
2. โ 1 โ 0 โ โ
3. โ 2 โ 0 โ โ
4. โ 5 โ 25 โ original โ
5. โ 6 โ 0 โ โ
6. โ 7 โ 0 โ โ
7. โ 10 โ 50 โ original โ
8. โ 11 โ 0 โ โ
9. โ 12 โ 0 โ โ
10. โ 15 โ 75 โ original โ
11. โ 16 โ 0 โ โ
12. โ 17 โ 0 โ โ
โโโโโโโดโโโโโโโโดโโโโโโโโโโโ
Example of a query without
INTERPOLATE
:
SELECT
n
,
source
,
inter
FROM
(
SELECT
toFloat32
(
number
%
10
)
AS
n
,
'original'
AS
source
,
number
AS
inter
FROM
numbers
(
10
)
WHERE
number
%
3
=
1
)
ORDER
BY
n
WITH
FILL
FROM
0
TO
5.51
STEP
0.5
;
Result:
โโโโnโโฌโsourceโโโโฌโinterโโ
โ 0 โ โ 0 โ
โ 0.5 โ โ 0 โ
โ 1 โ original โ 1 โ
โ 1.5 โ โ 0 โ
โ 2 โ โ 0 โ
โ 2.5 โ โ 0 โ
โ 3 โ โ 0 โ
โ 3.5 โ โ 0 โ
โ 4 โ original โ 4 โ
โ 4.5 โ โ 0 โ
โ 5 โ โ 0 โ
โ 5.5 โ โ 0 โ
โ 7 โ original โ 7 โ
โโโโโโโดโโโโโโโโโโโดโโโโโโโโ
Same query after applying
INTERPOLATE
:
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1);
Result:
โโโโnโโฌโsourceโโโโฌโinterโโ
โ 0 โ โ 0 โ
โ 0.5 โ โ 0 โ
โ 1 โ original โ 1 โ
โ 1.5 โ โ 2 โ
โ 2 โ โ 3 โ
โ 2.5 โ โ 4 โ
โ 3 โ โ 5 โ
โ 3.5 โ โ 6 โ
โ 4 โ original โ 4 โ
โ 4.5 โ โ 5 โ
โ 5 โ โ 6 โ
โ 5.5 โ โ 7 โ
โ 7 โ original โ 7 โ
โโโโโโโดโโโโโโโโโโโดโโโโโโโโ
Filling grouped by sorting prefix
โ
It can be useful to fill rows which have the same values in particular columns independently, - a good example is filling missing values in time series.
Assume there is the following time series table:
CREATE TABLE timeseries
(
`sensor_id` UInt64,
`timestamp` DateTime64(3, 'UTC'),
`value` Float64
)
ENGINE = Memory;
SELECT * FROM timeseries;
โโsensor_idโโฌโโโโโโโโโโโโโโโtimestampโโฌโvalueโโ
โ 234 โ 2021-12-01 00:00:03.000 โ 3 โ
โ 432 โ 2021-12-01 00:00:01.000 โ 1 โ
โ 234 โ 2021-12-01 00:00:07.000 โ 7 โ
โ 432 โ 2021-12-01 00:00:05.000 โ 5 โ
โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
And we'd like to fill missing values for each sensor independently with 1 second interval.
The way to achieve it is to use
sensor_id
column as sorting prefix for filling column
timestamp
:
SELECT *
FROM timeseries
ORDER BY
sensor_id,
timestamp WITH FILL
INTERPOLATE ( value AS 9999 )
โโsensor_idโโฌโโโโโโโโโโโโโโโtimestampโโฌโvalueโโ
โ 234 โ 2021-12-01 00:00:03.000 โ 3 โ
โ 234 โ 2021-12-01 00:00:04.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:05.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:06.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:07.000 โ 7 โ
โ 432 โ 2021-12-01 00:00:01.000 โ 1 โ
โ 432 โ 2021-12-01 00:00:02.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:03.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:04.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:05.000 โ 5 โ
โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
Here, the
value
column was interpolated with
9999
just to make filled rows more noticeable.
This behavior is controlled by setting
use_with_fill_by_sorting_prefix
(enabled by default)
Related content
โ
Blog:
Working with time series data in ClickHouse |
| Markdown | [Skip to main content](https://clickhouse.com/docs/sql-reference/statements/select/order-by#__docusaurus_skipToContent_fallback)
[](https://clickhouse.com/)
- [Products](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud)
- [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud)
- [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse)
- [Discover more than 100 integrations.](https://clickhouse.com/integrations)
[Discover more than 100 integrations.](https://clickhouse.com/integrations)
- [Use cases](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics)
- [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science)
- [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing)
- [Logs, Events, Traces](https://clickhouse.com/use-cases/observability)
- [All use cases](https://clickhouse.com/use-cases)
[All use cases](https://clickhouse.com/use-cases)
- [Documentation](https://clickhouse.com/docs)
- [Resources](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [User stories](https://clickhouse.com/user-stories)
- [Blog](https://clickhouse.com/blog)
- [Events](https://clickhouse.com/company/events)
- [Learning and certification](https://clickhouse.com/learn)
- [Comparison](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [BigQuery](https://clickhouse.com/comparison/bigquery)
- [PostgreSQL](https://clickhouse.com/comparison/postgresql)
- [Redshift](https://clickhouse.com/comparison/redshift)
- [Rockset](https://clickhouse.com/comparison/rockset)
- [Snowflake](https://clickhouse.com/comparison/snowflake)
- [Video](https://clickhouse.com/videos)
- [Demo](https://clickhouse.com/demos)
- [Pricing](https://clickhouse.com/pricing)
- [Contact](https://clickhouse.com/company/contact?loc=nav)
[46\.9k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)
[Search`Ctrl``K`](https://clickhouse.com/docs/search)
[Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=d14ae64b-e19f-4824-ba35-aa739bdce6fd&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&utm_ga=GA1.1.834547855.1776121359)
[Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=d14ae64b-e19f-4824-ba35-aa739bdce6fd&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&utm_ga=GA1.1.834547855.1776121359)
[Get started](https://clickhouse.com/docs/introduction-clickhouse)
[Cloud](https://clickhouse.com/docs/cloud/overview)
[Manage data](https://clickhouse.com/docs/updating-data)
[Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index)
[Reference](https://clickhouse.com/docs/sql-reference)
[Integrations](https://clickhouse.com/docs/integrations)
[ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview)
[chDB](https://clickhouse.com/docs/chdb)
[About](https://clickhouse.com/docs/about)
[Knowledge Base](https://clickhouse.com/docs/knowledgebase)
[English](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [English](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [ๆฅๆฌ่ช](https://clickhouse.com/docs/jp/sql-reference/statements/select/order-by)
- [ไธญๆ](https://clickhouse.com/docs/zh/sql-reference/statements/select/order-by)
- [ะ ัััะบะธะน](https://clickhouse.com/docs/ru/sql-reference/statements/select/order-by)
- [ํ๊ตญ์ด](https://clickhouse.com/docs/ko/sql-reference/statements/select/order-by)
[Skip to main content](https://clickhouse.com/docs/sql-reference/statements/select/order-by#__docusaurus_skipToContent_fallback)
[](https://clickhouse.com/)
- [Products](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud)
- [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud)
- [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse)
- [Discover more than 100 integrations.](https://clickhouse.com/integrations)
[Discover more than 100 integrations.](https://clickhouse.com/integrations)
- [Use cases](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics)
- [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science)
- [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing)
- [Logs, Events, Traces](https://clickhouse.com/use-cases/observability)
- [All use cases](https://clickhouse.com/use-cases)
[All use cases](https://clickhouse.com/use-cases)
- [Documentation](https://clickhouse.com/docs)
- [Resources](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [User stories](https://clickhouse.com/user-stories)
- [Blog](https://clickhouse.com/blog)
- [Events](https://clickhouse.com/company/events)
- [Learning and certification](https://clickhouse.com/learn)
- [Comparison](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [BigQuery](https://clickhouse.com/comparison/bigquery)
- [PostgreSQL](https://clickhouse.com/comparison/postgresql)
- [Redshift](https://clickhouse.com/comparison/redshift)
- [Rockset](https://clickhouse.com/comparison/rockset)
- [Snowflake](https://clickhouse.com/comparison/snowflake)
- [Video](https://clickhouse.com/videos)
- [Demo](https://clickhouse.com/demos)
- [Pricing](https://clickhouse.com/pricing)
- [Contact](https://clickhouse.com/company/contact?loc=nav)
[46\.9k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)
[Search`Ctrl``K`](https://clickhouse.com/docs/search)
[Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=d14ae64b-e19f-4824-ba35-aa739bdce6fd&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&utm_ga=GA1.1.834547855.1776121359)
[Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=d14ae64b-e19f-4824-ba35-aa739bdce6fd&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&utm_ga=GA1.1.834547855.1776121359)
[Get started](https://clickhouse.com/docs/introduction-clickhouse)
[Cloud](https://clickhouse.com/docs/cloud/overview)
[Manage data](https://clickhouse.com/docs/updating-data)
[Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index)
[Reference](https://clickhouse.com/docs/sql-reference)
[Integrations](https://clickhouse.com/docs/integrations)
[ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview)
[chDB](https://clickhouse.com/docs/chdb)
[About](https://clickhouse.com/docs/about)
[Knowledge Base](https://clickhouse.com/docs/knowledgebase)
[English](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [English](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [ๆฅๆฌ่ช](https://clickhouse.com/docs/jp/sql-reference/statements/select/order-by)
- [ไธญๆ](https://clickhouse.com/docs/zh/sql-reference/statements/select/order-by)
- [ะ ัััะบะธะน](https://clickhouse.com/docs/ru/sql-reference/statements/select/order-by)
- [ํ๊ตญ์ด](https://clickhouse.com/docs/ko/sql-reference/statements/select/order-by)
[Search`Ctrl``K`](https://clickhouse.com/docs/search)
- [Introduction](https://clickhouse.com/docs/sql-reference)
- [Syntax](https://clickhouse.com/docs/sql-reference/syntax)
- [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats)
- [Data types](https://clickhouse.com/docs/sql-reference/data-types)
- [Statements](https://clickhouse.com/docs/sql-reference/statements)
- [SELECT](https://clickhouse.com/docs/sql-reference/statements/select)
- [ALL](https://clickhouse.com/docs/sql-reference/statements/select/all)
- [APPLY](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier)
- [ARRAY JOIN](https://clickhouse.com/docs/sql-reference/statements/select/array-join)
- [DISTINCT](https://clickhouse.com/docs/sql-reference/statements/select/distinct)
- [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except)
- [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier)
- [FORMAT](https://clickhouse.com/docs/sql-reference/statements/select/format)
- [FROM](https://clickhouse.com/docs/sql-reference/statements/select/from)
- [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by)
- [HAVING](https://clickhouse.com/docs/sql-reference/statements/select/having)
- [INTERSECT](https://clickhouse.com/docs/sql-reference/statements/select/intersect)
- [INTO OUTFILE](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile)
- [JOIN](https://clickhouse.com/docs/sql-reference/statements/select/join)
- [LIMIT BY](https://clickhouse.com/docs/sql-reference/statements/select/limit-by)
- [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit)
- [OFFSET](https://clickhouse.com/docs/sql-reference/statements/select/offset)
- [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [PREWHERE](https://clickhouse.com/docs/sql-reference/statements/select/prewhere)
- [QUALIFY](https://clickhouse.com/docs/sql-reference/statements/select/qualify)
- [REPLACE](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier)
- [SAMPLE](https://clickhouse.com/docs/sql-reference/statements/select/sample)
- [UNION](https://clickhouse.com/docs/sql-reference/statements/select/union)
- [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where)
- [WITH](https://clickhouse.com/docs/sql-reference/statements/select/with)
- [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into)
- [CREATE](https://clickhouse.com/docs/sql-reference/statements/create)
- [ALTER](https://clickhouse.com/docs/sql-reference/statements/alter)
- [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete)
- [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system)
- [SHOW](https://clickhouse.com/docs/sql-reference/statements/show)
- [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant)
- [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain)
- [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke)
- [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update)
- [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach)
- [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table)
- [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table)
- [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach)
- [DROP](https://clickhouse.com/docs/sql-reference/statements/drop)
- [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists)
- [KILL](https://clickhouse.com/docs/sql-reference/statements/kill)
- [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize)
- [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename)
- [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange)
- [SET](https://clickhouse.com/docs/sql-reference/statements/set)
- [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role)
- [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate)
- [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as)
- [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with)
- [USE](https://clickhouse.com/docs/sql-reference/statements/use)
- [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch)
- [MOVE](https://clickhouse.com/docs/sql-reference/statements/move)
- [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant)
- [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop)
- [Operators](https://clickhouse.com/docs/sql-reference/operators)
- [Engines](https://clickhouse.com/docs/engines)
- [Database Engines](https://clickhouse.com/docs/engines/database-engines)
- [Table Engines](https://clickhouse.com/docs/engines/table-engines)
- [Functions](https://clickhouse.com/docs/sql-reference/functions)
- [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions)
- [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions)
- [Table functions](https://clickhouse.com/docs/sql-reference/table-functions)
- [Window functions](https://clickhouse.com/docs/sql-reference/window-functions)
- [Formats](https://clickhouse.com/docs/interfaces/formats)
- [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
- [Introduction](https://clickhouse.com/docs/sql-reference)
- [Statements](https://clickhouse.com/docs/sql-reference/statements)
- [SELECT](https://clickhouse.com/docs/sql-reference/statements/select)
- ORDER BY
[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/order-by.md)
# ORDER BY Clause
The `ORDER BY` clause contains
- a list of expressions, e.g. `ORDER BY visits, search_phrase`,
- a list of numbers referring to columns in the `SELECT` clause, e.g. `ORDER BY 2, 1`, or
- `ALL` which means all columns of the `SELECT` clause, e.g. `ORDER BY ALL`.
To disable sorting by column numbers, set setting [enable\_positional\_arguments](https://clickhouse.com/docs/operations/settings/settings#enable_positional_arguments) = 0. To disable sorting by `ALL`, set setting [enable\_order\_by\_all](https://clickhouse.com/docs/operations/settings/settings#enable_order_by_all) = 0.
The `ORDER BY` clause can be attributed by a `DESC` (descending) or `ASC` (ascending) modifier which determines the sorting direction. Unless an explicit sort order is specified, `ASC` is used by default. The sorting direction applies to a single expression, not to the entire list, e.g. `ORDER BY Visits DESC, SearchPhrase`. Also, sorting is performed case-sensitively.
Rows with identical values for a sort expressions are returned in an arbitrary and non-deterministic order. If the `ORDER BY` clause is omitted in a `SELECT` statement, the row order is also arbitrary and non-deterministic.
## Sorting of Special Values[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#sorting-of-special-values "Direct link to Sorting of Special Values")
There are two approaches to `NaN` and `NULL` sorting order:
- By default or with the `NULLS LAST` modifier: first the values, then `NaN`, then `NULL`.
- With the `NULLS FIRST` modifier: first `NULL`, then `NaN`, then other values.
### Example[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#example "Direct link to Example")
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.
## Collation Support[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#collation-support "Direct link to Collation Support")
For sorting by [String](https://clickhouse.com/docs/sql-reference/data-types/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 `ASC` or `DESC` is specified, `COLLATE` is specified after it. When using `COLLATE`, sorting is always case-insensitive.
Collate is supported in [LowCardinality](https://clickhouse.com/docs/sql-reference/data-types/lowcardinality), [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable), [Array](https://clickhouse.com/docs/sql-reference/data-types/array) and [Tuple](https://clickhouse.com/docs/sql-reference/data-types/tuple).
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.
## Collation Examples[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#collation-examples "Direct link to Collation Examples")
Example only with [String](https://clickhouse.com/docs/sql-reference/data-types/string) values:
Input table:
```
โโxโโฌโsโโโโโ
โ 1 โ bca โ
โ 2 โ ABC โ
โ 3 โ 123a โ
โ 4 โ abc โ
โ 5 โ BCA โ
โโโโโดโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโ
โ 3 โ 123a โ
โ 4 โ abc โ
โ 2 โ ABC โ
โ 1 โ bca โ
โ 5 โ BCA โ
โโโโโดโโโโโโโ
```
Example with [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable):
Input table:
```
โโxโโฌโsโโโโโ
โ 1 โ bca โ
โ 2 โ แดบแตแดธแดธ โ
โ 3 โ ABC โ
โ 4 โ 123a โ
โ 5 โ abc โ
โ 6 โ แดบแตแดธแดธ โ
โ 7 โ BCA โ
โโโโโดโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโ
โ 4 โ 123a โ
โ 5 โ abc โ
โ 3 โ ABC โ
โ 1 โ bca โ
โ 7 โ BCA โ
โ 6 โ แดบแตแดธแดธ โ
โ 2 โ แดบแตแดธแดธ โ
โโโโโดโโโโโโโ
```
Example with [Array](https://clickhouse.com/docs/sql-reference/data-types/array):
Input table:
```
โโxโโฌโsโโโโโโโโโโโโโโ
โ 1 โ ['Z'] โ
โ 2 โ ['z'] โ
โ 3 โ ['a'] โ
โ 4 โ ['A'] โ
โ 5 โ ['z','a'] โ
โ 6 โ ['z','a','a'] โ
โ 7 โ [''] โ
โโโโโดโโโโโโโโโโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโโโโโโโโโโ
โ 7 โ [''] โ
โ 3 โ ['a'] โ
โ 4 โ ['A'] โ
โ 2 โ ['z'] โ
โ 5 โ ['z','a'] โ
โ 6 โ ['z','a','a'] โ
โ 1 โ ['Z'] โ
โโโโโดโโโโโโโโโโโโโโโโ
```
Example with [LowCardinality](https://clickhouse.com/docs/sql-reference/data-types/lowcardinality) string:
Input table:
```
โโxโโฌโsโโโโ
โ 1 โ Z โ
โ 2 โ z โ
โ 3 โ a โ
โ 4 โ A โ
โ 5 โ za โ
โ 6 โ zaa โ
โ 7 โ โ
โโโโโดโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโ
โ 7 โ โ
โ 3 โ a โ
โ 4 โ A โ
โ 2 โ z โ
โ 1 โ Z โ
โ 5 โ za โ
โ 6 โ zaa โ
โโโโโดโโโโโโ
```
Example with [Tuple](https://clickhouse.com/docs/sql-reference/data-types/tuple):
```
โโxโโฌโsโโโโโโโโ
โ 1 โ (1,'Z') โ
โ 2 โ (1,'z') โ
โ 3 โ (1,'a') โ
โ 4 โ (2,'z') โ
โ 5 โ (1,'A') โ
โ 6 โ (2,'Z') โ
โ 7 โ (2,'A') โ
โโโโโดโโโโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโโโโ
โ 3 โ (1,'a') โ
โ 5 โ (1,'A') โ
โ 2 โ (1,'z') โ
โ 1 โ (1,'Z') โ
โ 7 โ (2,'A') โ
โ 4 โ (2,'z') โ
โ 6 โ (2,'Z') โ
โโโโโดโโโโโโโโโโ
```
## Implementation Details[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#implementation-details "Direct link to Implementation Details")
Less RAM is used if a small enough [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/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](https://clickhouse.com/docs/sql-reference/statements/select/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). You can also use spilling to disk only if query exceeds memory limits, i.e. `max_bytes_ratio_before_external_sort=0.6` will enable spilling to disk only once the query hits `60%` memory limit (user/sever).
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.
## Optimization of Data Reading[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#optimization-of-data-reading "Direct link to Optimization of Data Reading")
If `ORDER BY` expression has a prefix that coincides with the table sorting key, you can optimize the query by using the [optimize\_read\_in\_order](https://clickhouse.com/docs/operations/settings/settings#optimize_read_in_order) setting.
When the `optimize_read_in_order` setting is enabled, the ClickHouse server uses the table index and reads the data in order of the `ORDER BY` key. This allows to avoid reading all data in case of specified [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit). So queries on big data with small limit are processed faster.
Optimization works with both `ASC` and `DESC` and does not work together with [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) clause and [FINAL](https://clickhouse.com/docs/sql-reference/statements/select/from#final-modifier) modifier.
When the `optimize_read_in_order` setting is disabled, the ClickHouse server does not use the table index while processing `SELECT` queries.
Consider disabling `optimize_read_in_order` manually, when running queries that have `ORDER BY` clause, large `LIMIT` and [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where) condition that requires to read huge amount of records before queried data is found.
Optimization is supported in the following table engines:
- [MergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) (including [materialized views](https://clickhouse.com/docs/sql-reference/statements/create/view#materialized-view)),
- [Merge](https://clickhouse.com/docs/engines/table-engines/special/merge),
- [Buffer](https://clickhouse.com/docs/engines/table-engines/special/buffer)
In `MaterializedView`\-engine tables the optimization works with views like `SELECT ... FROM merge_tree_table ORDER BY pk`. But it is not supported in the queries like `SELECT ... FROM view ORDER BY pk` if the view query does not have the `ORDER BY` clause.
## ORDER BY Expr WITH FILL Modifier[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier "Direct link to ORDER BY Expr WITH FILL Modifier")
This modifier also can be combined with [LIMIT ... WITH TIES modifier](https://clickhouse.com/docs/sql-reference/statements/select/limit#limit--with-ties-modifier).
`WITH FILL` modifier can be set after `ORDER BY expr` with optional `FROM expr`, `TO expr` and `STEP expr` parameters. All missed values of `expr` column will be filled sequentially and other columns will be filled as defaults.
To fill multiple columns, add `WITH FILL` modifier with optional parameters after each field name in `ORDER BY` section.
```
ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr] [STALENESS const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr] [STALENESS numeric_expr]
[INTERPOLATE [(col [AS expr], ... colN [AS exprN])]]
```
`WITH FILL` can be applied for fields with Numeric (all kinds of float, decimal, int) or Date/DateTime types. When applied for `String` fields, missed values are filled with empty strings. When `FROM const_expr` not defined sequence of filling use minimal `expr` field value from `ORDER BY`. When `TO const_expr` not defined sequence of filling use maximum `expr` field value from `ORDER BY`. When `STEP const_numeric_expr` defined then `const_numeric_expr` interprets `as is` for numeric types, as `days` for Date type, as `seconds` for DateTime type. It also supports [INTERVAL](https://clickhouse.com/docs/sql-reference/data-types/special-data-types/interval) data type representing time and date intervals. When `STEP const_numeric_expr` omitted then sequence of filling use `1.0` for numeric type, `1 day` for Date type and `1 second` for DateTime type. When `STALENESS const_numeric_expr` is defined, the query will generate rows until the difference from the previous row in the original data exceeds `const_numeric_expr`. `INTERPOLATE` can be applied to columns not participating in `ORDER BY WITH FILL`. Such columns are filled based on previous fields values by applying `expr`. If `expr` is not present will repeat previous value. Omitted list will result in including all allowed columns.
Example of a query without `WITH FILL`:
```
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n;
```
Result:
```
โโnโโฌโsourceโโโโ
โ 1 โ original โ
โ 4 โ original โ
โ 7 โ original โ
โโโโโดโโโโโโโโโโโ
```
Same query after applying `WITH FILL` modifier:
```
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;
```
Result:
```
โโโโnโโฌโsourceโโโโ
โ 0 โ โ
โ 0.5 โ โ
โ 1 โ original โ
โ 1.5 โ โ
โ 2 โ โ
โ 2.5 โ โ
โ 3 โ โ
โ 3.5 โ โ
โ 4 โ original โ
โ 4.5 โ โ
โ 5 โ โ
โ 5.5 โ โ
โ 7 โ original โ
โโโโโโโดโโโโโโโโโโโ
```
For the case with multiple fields `ORDER BY field2 WITH FILL, field1 WITH FILL` order of filling will follow the order of fields in the `ORDER BY` clause.
Example:
```
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d2 WITH FILL,
d1 WITH FILL STEP 5;
```
Result:
```
โโโโd1โโโโโโโโฌโโโd2โโโโโโโโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-01 โ 1970-01-03 โ โ
โ 1970-01-01 โ 1970-01-04 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-01-01 โ 1970-01-06 โ โ
โ 1970-01-01 โ 1970-01-07 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
```
Field `d1` does not fill in and use the default value cause we do not have repeated values for `d2` value, and the sequence for `d1` can't be properly calculated.
The following query with the changed field in `ORDER BY`:
```
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP 5,
d2 WITH FILL;
```
Result:
```
โโโโd1โโโโโโโโฌโโโd2โโโโโโโโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-16 โ 1970-01-01 โ โ
โ 1970-01-21 โ 1970-01-01 โ โ
โ 1970-01-26 โ 1970-01-01 โ โ
โ 1970-01-31 โ 1970-01-01 โ โ
โ 1970-02-05 โ 1970-01-01 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-02-15 โ 1970-01-01 โ โ
โ 1970-02-20 โ 1970-01-01 โ โ
โ 1970-02-25 โ 1970-01-01 โ โ
โ 1970-03-02 โ 1970-01-01 โ โ
โ 1970-03-07 โ 1970-01-01 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
```
The following query uses the `INTERVAL` data type of 1 day for each data filled on column `d1`:
```
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP INTERVAL 1 DAY,
d2 WITH FILL;
```
Result:
```
โโโโโโโโโโd1โโฌโโโโโโโโโd2โโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-12 โ 1970-01-01 โ โ
โ 1970-01-13 โ 1970-01-01 โ โ
โ 1970-01-14 โ 1970-01-01 โ โ
โ 1970-01-15 โ 1970-01-01 โ โ
โ 1970-01-16 โ 1970-01-01 โ โ
โ 1970-01-17 โ 1970-01-01 โ โ
โ 1970-01-18 โ 1970-01-01 โ โ
โ 1970-01-19 โ 1970-01-01 โ โ
โ 1970-01-20 โ 1970-01-01 โ โ
โ 1970-01-21 โ 1970-01-01 โ โ
โ 1970-01-22 โ 1970-01-01 โ โ
โ 1970-01-23 โ 1970-01-01 โ โ
โ 1970-01-24 โ 1970-01-01 โ โ
โ 1970-01-25 โ 1970-01-01 โ โ
โ 1970-01-26 โ 1970-01-01 โ โ
โ 1970-01-27 โ 1970-01-01 โ โ
โ 1970-01-28 โ 1970-01-01 โ โ
โ 1970-01-29 โ 1970-01-01 โ โ
โ 1970-01-30 โ 1970-01-01 โ โ
โ 1970-01-31 โ 1970-01-01 โ โ
โ 1970-02-01 โ 1970-01-01 โ โ
โ 1970-02-02 โ 1970-01-01 โ โ
โ 1970-02-03 โ 1970-01-01 โ โ
โ 1970-02-04 โ 1970-01-01 โ โ
โ 1970-02-05 โ 1970-01-01 โ โ
โ 1970-02-06 โ 1970-01-01 โ โ
โ 1970-02-07 โ 1970-01-01 โ โ
โ 1970-02-08 โ 1970-01-01 โ โ
โ 1970-02-09 โ 1970-01-01 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-02-11 โ 1970-01-01 โ โ
โ 1970-02-12 โ 1970-01-01 โ โ
โ 1970-02-13 โ 1970-01-01 โ โ
โ 1970-02-14 โ 1970-01-01 โ โ
โ 1970-02-15 โ 1970-01-01 โ โ
โ 1970-02-16 โ 1970-01-01 โ โ
โ 1970-02-17 โ 1970-01-01 โ โ
โ 1970-02-18 โ 1970-01-01 โ โ
โ 1970-02-19 โ 1970-01-01 โ โ
โ 1970-02-20 โ 1970-01-01 โ โ
โ 1970-02-21 โ 1970-01-01 โ โ
โ 1970-02-22 โ 1970-01-01 โ โ
โ 1970-02-23 โ 1970-01-01 โ โ
โ 1970-02-24 โ 1970-01-01 โ โ
โ 1970-02-25 โ 1970-01-01 โ โ
โ 1970-02-26 โ 1970-01-01 โ โ
โ 1970-02-27 โ 1970-01-01 โ โ
โ 1970-02-28 โ 1970-01-01 โ โ
โ 1970-03-01 โ 1970-01-01 โ โ
โ 1970-03-02 โ 1970-01-01 โ โ
โ 1970-03-03 โ 1970-01-01 โ โ
โ 1970-03-04 โ 1970-01-01 โ โ
โ 1970-03-05 โ 1970-01-01 โ โ
โ 1970-03-06 โ 1970-01-01 โ โ
โ 1970-03-07 โ 1970-01-01 โ โ
โ 1970-03-08 โ 1970-01-01 โ โ
โ 1970-03-09 โ 1970-01-01 โ โ
โ 1970-03-10 โ 1970-01-01 โ โ
โ 1970-03-11 โ 1970-01-01 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
```
Example of a query without `STALENESS`:
```
SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL;
```
Result:
```
โโkeyโโฌโvalueโโฌโsourceโโโโ
1. โ 0 โ 0 โ original โ
2. โ 1 โ 0 โ โ
3. โ 2 โ 0 โ โ
4. โ 3 โ 0 โ โ
5. โ 4 โ 0 โ โ
6. โ 5 โ 25 โ original โ
7. โ 6 โ 0 โ โ
8. โ 7 โ 0 โ โ
9. โ 8 โ 0 โ โ
10. โ 9 โ 0 โ โ
11. โ 10 โ 50 โ original โ
12. โ 11 โ 0 โ โ
13. โ 12 โ 0 โ โ
14. โ 13 โ 0 โ โ
15. โ 14 โ 0 โ โ
16. โ 15 โ 75 โ original โ
โโโโโโโดโโโโโโโโดโโโโโโโโโโโ
```
Same query after applying `STALENESS 3`:
```
SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL STALENESS 3;
```
Result:
```
โโkeyโโฌโvalueโโฌโsourceโโโโ
1. โ 0 โ 0 โ original โ
2. โ 1 โ 0 โ โ
3. โ 2 โ 0 โ โ
4. โ 5 โ 25 โ original โ
5. โ 6 โ 0 โ โ
6. โ 7 โ 0 โ โ
7. โ 10 โ 50 โ original โ
8. โ 11 โ 0 โ โ
9. โ 12 โ 0 โ โ
10. โ 15 โ 75 โ original โ
11. โ 16 โ 0 โ โ
12. โ 17 โ 0 โ โ
โโโโโโโดโโโโโโโโดโโโโโโโโโโโ
```
Example of a query without `INTERPOLATE`:
```
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;
```
Result:
```
โโโโnโโฌโsourceโโโโฌโinterโโ
โ 0 โ โ 0 โ
โ 0.5 โ โ 0 โ
โ 1 โ original โ 1 โ
โ 1.5 โ โ 0 โ
โ 2 โ โ 0 โ
โ 2.5 โ โ 0 โ
โ 3 โ โ 0 โ
โ 3.5 โ โ 0 โ
โ 4 โ original โ 4 โ
โ 4.5 โ โ 0 โ
โ 5 โ โ 0 โ
โ 5.5 โ โ 0 โ
โ 7 โ original โ 7 โ
โโโโโโโดโโโโโโโโโโโดโโโโโโโโ
```
Same query after applying `INTERPOLATE`:
```
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1);
```
Result:
```
โโโโnโโฌโsourceโโโโฌโinterโโ
โ 0 โ โ 0 โ
โ 0.5 โ โ 0 โ
โ 1 โ original โ 1 โ
โ 1.5 โ โ 2 โ
โ 2 โ โ 3 โ
โ 2.5 โ โ 4 โ
โ 3 โ โ 5 โ
โ 3.5 โ โ 6 โ
โ 4 โ original โ 4 โ
โ 4.5 โ โ 5 โ
โ 5 โ โ 6 โ
โ 5.5 โ โ 7 โ
โ 7 โ original โ 7 โ
โโโโโโโดโโโโโโโโโโโดโโโโโโโโ
```
## Filling grouped by sorting prefix[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#filling-grouped-by-sorting-prefix "Direct link to Filling grouped by sorting prefix")
It can be useful to fill rows which have the same values in particular columns independently, - a good example is filling missing values in time series. Assume there is the following time series table:
```
CREATE TABLE timeseries
(
`sensor_id` UInt64,
`timestamp` DateTime64(3, 'UTC'),
`value` Float64
)
ENGINE = Memory;
SELECT * FROM timeseries;
โโsensor_idโโฌโโโโโโโโโโโโโโโtimestampโโฌโvalueโโ
โ 234 โ 2021-12-01 00:00:03.000 โ 3 โ
โ 432 โ 2021-12-01 00:00:01.000 โ 1 โ
โ 234 โ 2021-12-01 00:00:07.000 โ 7 โ
โ 432 โ 2021-12-01 00:00:05.000 โ 5 โ
โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
```
And we'd like to fill missing values for each sensor independently with 1 second interval. The way to achieve it is to use `sensor_id` column as sorting prefix for filling column `timestamp`:
```
SELECT *
FROM timeseries
ORDER BY
sensor_id,
timestamp WITH FILL
INTERPOLATE ( value AS 9999 )
โโsensor_idโโฌโโโโโโโโโโโโโโโtimestampโโฌโvalueโโ
โ 234 โ 2021-12-01 00:00:03.000 โ 3 โ
โ 234 โ 2021-12-01 00:00:04.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:05.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:06.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:07.000 โ 7 โ
โ 432 โ 2021-12-01 00:00:01.000 โ 1 โ
โ 432 โ 2021-12-01 00:00:02.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:03.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:04.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:05.000 โ 5 โ
โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
```
Here, the `value` column was interpolated with `9999` just to make filled rows more noticeable. This behavior is controlled by setting `use_with_fill_by_sorting_prefix` (enabled by default)
## Related content[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#related-content "Direct link to Related content")
- Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse)
[Previous OFFSET](https://clickhouse.com/docs/sql-reference/statements/select/offset)
[Next PREWHERE](https://clickhouse.com/docs/sql-reference/statements/select/prewhere)
- [Sorting of Special Values](https://clickhouse.com/docs/sql-reference/statements/select/order-by#sorting-of-special-values)
- [Example](https://clickhouse.com/docs/sql-reference/statements/select/order-by#example)
- [Collation Support](https://clickhouse.com/docs/sql-reference/statements/select/order-by#collation-support)
- [Collation Examples](https://clickhouse.com/docs/sql-reference/statements/select/order-by#collation-examples)
- [Implementation Details](https://clickhouse.com/docs/sql-reference/statements/select/order-by#implementation-details)
- [Optimization of Data Reading](https://clickhouse.com/docs/sql-reference/statements/select/order-by#optimization-of-data-reading)
- [ORDER BY Expr WITH FILL Modifier](https://clickhouse.com/docs/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier)
- [Filling grouped by sorting prefix](https://clickhouse.com/docs/sql-reference/statements/select/order-by#filling-grouped-by-sorting-prefix)
- [Related content](https://clickhouse.com/docs/sql-reference/statements/select/order-by#related-content)
Was this page helpful?
###### Try ClickHouse Cloud for FREE
Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up.
[Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=d14ae64b-e19f-4824-ba35-aa739bdce6fd&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Forder-by&utm_ga=GA1.1.834547855.1776121359)
ยฉ 2016โ2026 ClickHouse, Inc.
[Trademark](https://clickhouse.com/legal/trademark-policy)ยท[Privacy](https://clickhouse.com/legal/privacy-policy)ยท[Security](https://trust.clickhouse.com/)ยท[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service)
ยฉ 2016โ2026 ClickHouse, Inc.
[Trademark](https://clickhouse.com/legal/trademark-policy)ยท[Privacy](https://clickhouse.com/legal/privacy-policy)ยท[Security](https://trust.clickhouse.com/)ยท[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service)
[](https://clickhouse.com/)
EN
- Get startedโผ
- Cloudโผ
- Manage dataโผ
- Server adminโผ
- Referenceโผ
- Integrationsโผ
- ClickStackโผ
- chDBโผ
- Aboutโผ
[](https://clickhouse.com/)
EN
main-menu
- Introductionโผ
- [Syntax](https://clickhouse.com/docs/sql-reference/syntax)
- [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats)
- Data typesโผ
- Statementsโผ
- SELECTโผ
- [ALL](https://clickhouse.com/docs/sql-reference/statements/select/all)
- [APPLY](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier)
- [ARRAY JOIN](https://clickhouse.com/docs/sql-reference/statements/select/array-join)
- [DISTINCT](https://clickhouse.com/docs/sql-reference/statements/select/distinct)
- [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except)
- [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier)
- [FORMAT](https://clickhouse.com/docs/sql-reference/statements/select/format)
- [FROM](https://clickhouse.com/docs/sql-reference/statements/select/from)
- [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by)
- [HAVING](https://clickhouse.com/docs/sql-reference/statements/select/having)
- [INTERSECT](https://clickhouse.com/docs/sql-reference/statements/select/intersect)
- [INTO OUTFILE](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile)
- [JOIN](https://clickhouse.com/docs/sql-reference/statements/select/join)
- [LIMIT BY](https://clickhouse.com/docs/sql-reference/statements/select/limit-by)
- [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit)
- [OFFSET](https://clickhouse.com/docs/sql-reference/statements/select/offset)
- [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by)
- [PREWHERE](https://clickhouse.com/docs/sql-reference/statements/select/prewhere)
- [QUALIFY](https://clickhouse.com/docs/sql-reference/statements/select/qualify)
- [REPLACE](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier)
- [SAMPLE](https://clickhouse.com/docs/sql-reference/statements/select/sample)
- [UNION](https://clickhouse.com/docs/sql-reference/statements/select/union)
- [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where)
- [WITH](https://clickhouse.com/docs/sql-reference/statements/select/with)
- [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into)
- CREATEโผ
- ALTERโผ
- [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete)
- [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system)
- [SHOW](https://clickhouse.com/docs/sql-reference/statements/show)
- [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant)
- [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain)
- [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke)
- [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update)
- [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach)
- [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table)
- [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table)
- [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach)
- [DROP](https://clickhouse.com/docs/sql-reference/statements/drop)
- [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists)
- [KILL](https://clickhouse.com/docs/sql-reference/statements/kill)
- [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize)
- [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename)
- [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange)
- [SET](https://clickhouse.com/docs/sql-reference/statements/set)
- [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role)
- [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate)
- [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as)
- [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with)
- [USE](https://clickhouse.com/docs/sql-reference/statements/use)
- [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch)
- [MOVE](https://clickhouse.com/docs/sql-reference/statements/move)
- [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant)
- [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop)
- Operatorsโผ
- Enginesโผ
- Functionsโผ
- Formatsโผ
- [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) |
| Readable Markdown | The `ORDER BY` clause contains
- a list of expressions, e.g. `ORDER BY visits, search_phrase`,
- a list of numbers referring to columns in the `SELECT` clause, e.g. `ORDER BY 2, 1`, or
- `ALL` which means all columns of the `SELECT` clause, e.g. `ORDER BY ALL`.
To disable sorting by column numbers, set setting [enable\_positional\_arguments](https://clickhouse.com/docs/operations/settings/settings#enable_positional_arguments) = 0. To disable sorting by `ALL`, set setting [enable\_order\_by\_all](https://clickhouse.com/docs/operations/settings/settings#enable_order_by_all) = 0.
The `ORDER BY` clause can be attributed by a `DESC` (descending) or `ASC` (ascending) modifier which determines the sorting direction. Unless an explicit sort order is specified, `ASC` is used by default. The sorting direction applies to a single expression, not to the entire list, e.g. `ORDER BY Visits DESC, SearchPhrase`. Also, sorting is performed case-sensitively.
Rows with identical values for a sort expressions are returned in an arbitrary and non-deterministic order. If the `ORDER BY` clause is omitted in a `SELECT` statement, the row order is also arbitrary and non-deterministic.
## Sorting of Special Values[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#sorting-of-special-values "Direct link to Sorting of Special Values")
There are two approaches to `NaN` and `NULL` sorting order:
- By default or with the `NULLS LAST` modifier: first the values, then `NaN`, then `NULL`.
- With the `NULLS FIRST` modifier: first `NULL`, then `NaN`, then other values.
### Example[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#example "Direct link to Example")
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.
## Collation Support[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#collation-support "Direct link to Collation Support")
For sorting by [String](https://clickhouse.com/docs/sql-reference/data-types/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 `ASC` or `DESC` is specified, `COLLATE` is specified after it. When using `COLLATE`, sorting is always case-insensitive.
Collate is supported in [LowCardinality](https://clickhouse.com/docs/sql-reference/data-types/lowcardinality), [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable), [Array](https://clickhouse.com/docs/sql-reference/data-types/array) and [Tuple](https://clickhouse.com/docs/sql-reference/data-types/tuple).
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.
## Collation Examples[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#collation-examples "Direct link to Collation Examples")
Example only with [String](https://clickhouse.com/docs/sql-reference/data-types/string) values:
Input table:
```
โโxโโฌโsโโโโโ
โ 1 โ bca โ
โ 2 โ ABC โ
โ 3 โ 123a โ
โ 4 โ abc โ
โ 5 โ BCA โ
โโโโโดโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโ
โ 3 โ 123a โ
โ 4 โ abc โ
โ 2 โ ABC โ
โ 1 โ bca โ
โ 5 โ BCA โ
โโโโโดโโโโโโโ
```
Example with [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable):
Input table:
```
โโxโโฌโsโโโโโ
โ 1 โ bca โ
โ 2 โ แดบแตแดธแดธ โ
โ 3 โ ABC โ
โ 4 โ 123a โ
โ 5 โ abc โ
โ 6 โ แดบแตแดธแดธ โ
โ 7 โ BCA โ
โโโโโดโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโ
โ 4 โ 123a โ
โ 5 โ abc โ
โ 3 โ ABC โ
โ 1 โ bca โ
โ 7 โ BCA โ
โ 6 โ แดบแตแดธแดธ โ
โ 2 โ แดบแตแดธแดธ โ
โโโโโดโโโโโโโ
```
Example with [Array](https://clickhouse.com/docs/sql-reference/data-types/array):
Input table:
```
โโxโโฌโsโโโโโโโโโโโโโโ
โ 1 โ ['Z'] โ
โ 2 โ ['z'] โ
โ 3 โ ['a'] โ
โ 4 โ ['A'] โ
โ 5 โ ['z','a'] โ
โ 6 โ ['z','a','a'] โ
โ 7 โ [''] โ
โโโโโดโโโโโโโโโโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโโโโโโโโโโ
โ 7 โ [''] โ
โ 3 โ ['a'] โ
โ 4 โ ['A'] โ
โ 2 โ ['z'] โ
โ 5 โ ['z','a'] โ
โ 6 โ ['z','a','a'] โ
โ 1 โ ['Z'] โ
โโโโโดโโโโโโโโโโโโโโโโ
```
Example with [LowCardinality](https://clickhouse.com/docs/sql-reference/data-types/lowcardinality) string:
Input table:
```
โโxโโฌโsโโโโ
โ 1 โ Z โ
โ 2 โ z โ
โ 3 โ a โ
โ 4 โ A โ
โ 5 โ za โ
โ 6 โ zaa โ
โ 7 โ โ
โโโโโดโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโ
โ 7 โ โ
โ 3 โ a โ
โ 4 โ A โ
โ 2 โ z โ
โ 1 โ Z โ
โ 5 โ za โ
โ 6 โ zaa โ
โโโโโดโโโโโโ
```
Example with [Tuple](https://clickhouse.com/docs/sql-reference/data-types/tuple):
```
โโxโโฌโsโโโโโโโโ
โ 1 โ (1,'Z') โ
โ 2 โ (1,'z') โ
โ 3 โ (1,'a') โ
โ 4 โ (2,'z') โ
โ 5 โ (1,'A') โ
โ 6 โ (2,'Z') โ
โ 7 โ (2,'A') โ
โโโโโดโโโโโโโโโโ
```
Query:
```
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
```
Result:
```
โโxโโฌโsโโโโโโโโ
โ 3 โ (1,'a') โ
โ 5 โ (1,'A') โ
โ 2 โ (1,'z') โ
โ 1 โ (1,'Z') โ
โ 7 โ (2,'A') โ
โ 4 โ (2,'z') โ
โ 6 โ (2,'Z') โ
โโโโโดโโโโโโโโโโ
```
## Implementation Details[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#implementation-details "Direct link to Implementation Details")
Less RAM is used if a small enough [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/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](https://clickhouse.com/docs/sql-reference/statements/select/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). You can also use spilling to disk only if query exceeds memory limits, i.e. `max_bytes_ratio_before_external_sort=0.6` will enable spilling to disk only once the query hits `60%` memory limit (user/sever).
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.
## Optimization of Data Reading[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#optimization-of-data-reading "Direct link to Optimization of Data Reading")
If `ORDER BY` expression has a prefix that coincides with the table sorting key, you can optimize the query by using the [optimize\_read\_in\_order](https://clickhouse.com/docs/operations/settings/settings#optimize_read_in_order) setting.
When the `optimize_read_in_order` setting is enabled, the ClickHouse server uses the table index and reads the data in order of the `ORDER BY` key. This allows to avoid reading all data in case of specified [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit). So queries on big data with small limit are processed faster.
Optimization works with both `ASC` and `DESC` and does not work together with [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) clause and [FINAL](https://clickhouse.com/docs/sql-reference/statements/select/from#final-modifier) modifier.
When the `optimize_read_in_order` setting is disabled, the ClickHouse server does not use the table index while processing `SELECT` queries.
Consider disabling `optimize_read_in_order` manually, when running queries that have `ORDER BY` clause, large `LIMIT` and [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where) condition that requires to read huge amount of records before queried data is found.
Optimization is supported in the following table engines:
- [MergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) (including [materialized views](https://clickhouse.com/docs/sql-reference/statements/create/view#materialized-view)),
- [Merge](https://clickhouse.com/docs/engines/table-engines/special/merge),
- [Buffer](https://clickhouse.com/docs/engines/table-engines/special/buffer)
In `MaterializedView`\-engine tables the optimization works with views like `SELECT ... FROM merge_tree_table ORDER BY pk`. But it is not supported in the queries like `SELECT ... FROM view ORDER BY pk` if the view query does not have the `ORDER BY` clause.
## ORDER BY Expr WITH FILL Modifier[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier "Direct link to ORDER BY Expr WITH FILL Modifier")
This modifier also can be combined with [LIMIT ... WITH TIES modifier](https://clickhouse.com/docs/sql-reference/statements/select/limit#limit--with-ties-modifier).
`WITH FILL` modifier can be set after `ORDER BY expr` with optional `FROM expr`, `TO expr` and `STEP expr` parameters. All missed values of `expr` column will be filled sequentially and other columns will be filled as defaults.
To fill multiple columns, add `WITH FILL` modifier with optional parameters after each field name in `ORDER BY` section.
```
ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr] [STALENESS const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr] [STALENESS numeric_expr]
[INTERPOLATE [(col [AS expr], ... colN [AS exprN])]]
```
`WITH FILL` can be applied for fields with Numeric (all kinds of float, decimal, int) or Date/DateTime types. When applied for `String` fields, missed values are filled with empty strings. When `FROM const_expr` not defined sequence of filling use minimal `expr` field value from `ORDER BY`. When `TO const_expr` not defined sequence of filling use maximum `expr` field value from `ORDER BY`. When `STEP const_numeric_expr` defined then `const_numeric_expr` interprets `as is` for numeric types, as `days` for Date type, as `seconds` for DateTime type. It also supports [INTERVAL](https://clickhouse.com/docs/sql-reference/data-types/special-data-types/interval) data type representing time and date intervals. When `STEP const_numeric_expr` omitted then sequence of filling use `1.0` for numeric type, `1 day` for Date type and `1 second` for DateTime type. When `STALENESS const_numeric_expr` is defined, the query will generate rows until the difference from the previous row in the original data exceeds `const_numeric_expr`. `INTERPOLATE` can be applied to columns not participating in `ORDER BY WITH FILL`. Such columns are filled based on previous fields values by applying `expr`. If `expr` is not present will repeat previous value. Omitted list will result in including all allowed columns.
Example of a query without `WITH FILL`:
```
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n;
```
Result:
```
โโnโโฌโsourceโโโโ
โ 1 โ original โ
โ 4 โ original โ
โ 7 โ original โ
โโโโโดโโโโโโโโโโโ
```
Same query after applying `WITH FILL` modifier:
```
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;
```
Result:
```
โโโโnโโฌโsourceโโโโ
โ 0 โ โ
โ 0.5 โ โ
โ 1 โ original โ
โ 1.5 โ โ
โ 2 โ โ
โ 2.5 โ โ
โ 3 โ โ
โ 3.5 โ โ
โ 4 โ original โ
โ 4.5 โ โ
โ 5 โ โ
โ 5.5 โ โ
โ 7 โ original โ
โโโโโโโดโโโโโโโโโโโ
```
For the case with multiple fields `ORDER BY field2 WITH FILL, field1 WITH FILL` order of filling will follow the order of fields in the `ORDER BY` clause.
Example:
```
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d2 WITH FILL,
d1 WITH FILL STEP 5;
```
Result:
```
โโโโd1โโโโโโโโฌโโโd2โโโโโโโโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-01 โ 1970-01-03 โ โ
โ 1970-01-01 โ 1970-01-04 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-01-01 โ 1970-01-06 โ โ
โ 1970-01-01 โ 1970-01-07 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
```
Field `d1` does not fill in and use the default value cause we do not have repeated values for `d2` value, and the sequence for `d1` can't be properly calculated.
The following query with the changed field in `ORDER BY`:
```
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP 5,
d2 WITH FILL;
```
Result:
```
โโโโd1โโโโโโโโฌโโโd2โโโโโโโโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-16 โ 1970-01-01 โ โ
โ 1970-01-21 โ 1970-01-01 โ โ
โ 1970-01-26 โ 1970-01-01 โ โ
โ 1970-01-31 โ 1970-01-01 โ โ
โ 1970-02-05 โ 1970-01-01 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-02-15 โ 1970-01-01 โ โ
โ 1970-02-20 โ 1970-01-01 โ โ
โ 1970-02-25 โ 1970-01-01 โ โ
โ 1970-03-02 โ 1970-01-01 โ โ
โ 1970-03-07 โ 1970-01-01 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
```
The following query uses the `INTERVAL` data type of 1 day for each data filled on column `d1`:
```
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP INTERVAL 1 DAY,
d2 WITH FILL;
```
Result:
```
โโโโโโโโโโd1โโฌโโโโโโโโโd2โโฌโsourceโโโโ
โ 1970-01-11 โ 1970-01-02 โ original โ
โ 1970-01-12 โ 1970-01-01 โ โ
โ 1970-01-13 โ 1970-01-01 โ โ
โ 1970-01-14 โ 1970-01-01 โ โ
โ 1970-01-15 โ 1970-01-01 โ โ
โ 1970-01-16 โ 1970-01-01 โ โ
โ 1970-01-17 โ 1970-01-01 โ โ
โ 1970-01-18 โ 1970-01-01 โ โ
โ 1970-01-19 โ 1970-01-01 โ โ
โ 1970-01-20 โ 1970-01-01 โ โ
โ 1970-01-21 โ 1970-01-01 โ โ
โ 1970-01-22 โ 1970-01-01 โ โ
โ 1970-01-23 โ 1970-01-01 โ โ
โ 1970-01-24 โ 1970-01-01 โ โ
โ 1970-01-25 โ 1970-01-01 โ โ
โ 1970-01-26 โ 1970-01-01 โ โ
โ 1970-01-27 โ 1970-01-01 โ โ
โ 1970-01-28 โ 1970-01-01 โ โ
โ 1970-01-29 โ 1970-01-01 โ โ
โ 1970-01-30 โ 1970-01-01 โ โ
โ 1970-01-31 โ 1970-01-01 โ โ
โ 1970-02-01 โ 1970-01-01 โ โ
โ 1970-02-02 โ 1970-01-01 โ โ
โ 1970-02-03 โ 1970-01-01 โ โ
โ 1970-02-04 โ 1970-01-01 โ โ
โ 1970-02-05 โ 1970-01-01 โ โ
โ 1970-02-06 โ 1970-01-01 โ โ
โ 1970-02-07 โ 1970-01-01 โ โ
โ 1970-02-08 โ 1970-01-01 โ โ
โ 1970-02-09 โ 1970-01-01 โ โ
โ 1970-02-10 โ 1970-01-05 โ original โ
โ 1970-02-11 โ 1970-01-01 โ โ
โ 1970-02-12 โ 1970-01-01 โ โ
โ 1970-02-13 โ 1970-01-01 โ โ
โ 1970-02-14 โ 1970-01-01 โ โ
โ 1970-02-15 โ 1970-01-01 โ โ
โ 1970-02-16 โ 1970-01-01 โ โ
โ 1970-02-17 โ 1970-01-01 โ โ
โ 1970-02-18 โ 1970-01-01 โ โ
โ 1970-02-19 โ 1970-01-01 โ โ
โ 1970-02-20 โ 1970-01-01 โ โ
โ 1970-02-21 โ 1970-01-01 โ โ
โ 1970-02-22 โ 1970-01-01 โ โ
โ 1970-02-23 โ 1970-01-01 โ โ
โ 1970-02-24 โ 1970-01-01 โ โ
โ 1970-02-25 โ 1970-01-01 โ โ
โ 1970-02-26 โ 1970-01-01 โ โ
โ 1970-02-27 โ 1970-01-01 โ โ
โ 1970-02-28 โ 1970-01-01 โ โ
โ 1970-03-01 โ 1970-01-01 โ โ
โ 1970-03-02 โ 1970-01-01 โ โ
โ 1970-03-03 โ 1970-01-01 โ โ
โ 1970-03-04 โ 1970-01-01 โ โ
โ 1970-03-05 โ 1970-01-01 โ โ
โ 1970-03-06 โ 1970-01-01 โ โ
โ 1970-03-07 โ 1970-01-01 โ โ
โ 1970-03-08 โ 1970-01-01 โ โ
โ 1970-03-09 โ 1970-01-01 โ โ
โ 1970-03-10 โ 1970-01-01 โ โ
โ 1970-03-11 โ 1970-01-01 โ โ
โ 1970-03-12 โ 1970-01-08 โ original โ
โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโ
```
Example of a query without `STALENESS`:
```
SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL;
```
Result:
```
โโkeyโโฌโvalueโโฌโsourceโโโโ
1. โ 0 โ 0 โ original โ
2. โ 1 โ 0 โ โ
3. โ 2 โ 0 โ โ
4. โ 3 โ 0 โ โ
5. โ 4 โ 0 โ โ
6. โ 5 โ 25 โ original โ
7. โ 6 โ 0 โ โ
8. โ 7 โ 0 โ โ
9. โ 8 โ 0 โ โ
10. โ 9 โ 0 โ โ
11. โ 10 โ 50 โ original โ
12. โ 11 โ 0 โ โ
13. โ 12 โ 0 โ โ
14. โ 13 โ 0 โ โ
15. โ 14 โ 0 โ โ
16. โ 15 โ 75 โ original โ
โโโโโโโดโโโโโโโโดโโโโโโโโโโโ
```
Same query after applying `STALENESS 3`:
```
SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL STALENESS 3;
```
Result:
```
โโkeyโโฌโvalueโโฌโsourceโโโโ
1. โ 0 โ 0 โ original โ
2. โ 1 โ 0 โ โ
3. โ 2 โ 0 โ โ
4. โ 5 โ 25 โ original โ
5. โ 6 โ 0 โ โ
6. โ 7 โ 0 โ โ
7. โ 10 โ 50 โ original โ
8. โ 11 โ 0 โ โ
9. โ 12 โ 0 โ โ
10. โ 15 โ 75 โ original โ
11. โ 16 โ 0 โ โ
12. โ 17 โ 0 โ โ
โโโโโโโดโโโโโโโโดโโโโโโโโโโโ
```
Example of a query without `INTERPOLATE`:
```
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;
```
Result:
```
โโโโnโโฌโsourceโโโโฌโinterโโ
โ 0 โ โ 0 โ
โ 0.5 โ โ 0 โ
โ 1 โ original โ 1 โ
โ 1.5 โ โ 0 โ
โ 2 โ โ 0 โ
โ 2.5 โ โ 0 โ
โ 3 โ โ 0 โ
โ 3.5 โ โ 0 โ
โ 4 โ original โ 4 โ
โ 4.5 โ โ 0 โ
โ 5 โ โ 0 โ
โ 5.5 โ โ 0 โ
โ 7 โ original โ 7 โ
โโโโโโโดโโโโโโโโโโโดโโโโโโโโ
```
Same query after applying `INTERPOLATE`:
```
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1);
```
Result:
```
โโโโnโโฌโsourceโโโโฌโinterโโ
โ 0 โ โ 0 โ
โ 0.5 โ โ 0 โ
โ 1 โ original โ 1 โ
โ 1.5 โ โ 2 โ
โ 2 โ โ 3 โ
โ 2.5 โ โ 4 โ
โ 3 โ โ 5 โ
โ 3.5 โ โ 6 โ
โ 4 โ original โ 4 โ
โ 4.5 โ โ 5 โ
โ 5 โ โ 6 โ
โ 5.5 โ โ 7 โ
โ 7 โ original โ 7 โ
โโโโโโโดโโโโโโโโโโโดโโโโโโโโ
```
## Filling grouped by sorting prefix[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#filling-grouped-by-sorting-prefix "Direct link to Filling grouped by sorting prefix")
It can be useful to fill rows which have the same values in particular columns independently, - a good example is filling missing values in time series. Assume there is the following time series table:
```
CREATE TABLE timeseries
(
`sensor_id` UInt64,
`timestamp` DateTime64(3, 'UTC'),
`value` Float64
)
ENGINE = Memory;
SELECT * FROM timeseries;
โโsensor_idโโฌโโโโโโโโโโโโโโโtimestampโโฌโvalueโโ
โ 234 โ 2021-12-01 00:00:03.000 โ 3 โ
โ 432 โ 2021-12-01 00:00:01.000 โ 1 โ
โ 234 โ 2021-12-01 00:00:07.000 โ 7 โ
โ 432 โ 2021-12-01 00:00:05.000 โ 5 โ
โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
```
And we'd like to fill missing values for each sensor independently with 1 second interval. The way to achieve it is to use `sensor_id` column as sorting prefix for filling column `timestamp`:
```
SELECT *
FROM timeseries
ORDER BY
sensor_id,
timestamp WITH FILL
INTERPOLATE ( value AS 9999 )
โโsensor_idโโฌโโโโโโโโโโโโโโโtimestampโโฌโvalueโโ
โ 234 โ 2021-12-01 00:00:03.000 โ 3 โ
โ 234 โ 2021-12-01 00:00:04.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:05.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:06.000 โ 9999 โ
โ 234 โ 2021-12-01 00:00:07.000 โ 7 โ
โ 432 โ 2021-12-01 00:00:01.000 โ 1 โ
โ 432 โ 2021-12-01 00:00:02.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:03.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:04.000 โ 9999 โ
โ 432 โ 2021-12-01 00:00:05.000 โ 5 โ
โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
```
Here, the `value` column was interpolated with `9999` just to make filled rows more noticeable. This behavior is controlled by setting `use_with_fill_by_sorting_prefix` (enabled by default)
## Related content[โ](https://clickhouse.com/docs/sql-reference/statements/select/order-by#related-content "Direct link to Related content")
- Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) |
| Shard | 89 (laksa) |
| Root Hash | 12633450985039531489 |
| Unparsed URL | com,clickhouse!/docs/sql-reference/statements/select/order-by s443 |