You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The documentation is fairly brief on SELECT DISTINCT ON (col1,col2), in particular it does not say whether order matters when the distinct-relation is applied.
The section in the documentation on DISTINCT and ORDER BY, however, mentions that
The DISTINCT clause is executed before the ORDER BY clause.
This does not appear to be the case for DISTINCT ON:
create table tmp_distinct_table (
ts UInt32,
ver UInt32,
value UInt32
) engine = MergeTree()
order by (ts, ver);
insert into tmp_distinct_table values
(1, 1, 10),
(1, 2, 20),
(2, 1, 5),
(3, 2, 30),
(3, 1, 30),
(4, 2, 40),
(4, 1, 41),
(4, 3, 42);
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
https://clickhouse.com/docs/en/sql-reference/statements/select/distinct
from issue #25404 and pull request #25589
The documentation is fairly brief on
SELECT DISTINCT ON (col1,col2)
, in particular it does not say whether order matters when the distinct-relation is applied.The section in the documentation on
DISTINCT and ORDER BY
, however, mentions thatThis does not appear to be the case for
DISTINCT ON
:This table now contains
Take the following query, aiming to select unique
ts
with the highest value forver
:The result is
Which from
ver=1
may indicate that eitherDISTINCT ON(ver)
.ORDER BY
-clause was applied before applyingDISTINCT ON
(contrary to what the documentation claims forDISTINCT
).Take this second query (the only difference is appending
ver desc
at the very end in the last sort):The result is
This is the result I expected from my initial understanding of the documentation.
My questions are
DISTINCT
andDISTINCT ON
clauses?ORDER BY
is in fact applied beforeDISTINCT ON
, even if this is not the case forDISTINCT
?Beta Was this translation helpful? Give feedback.
All reactions