argMax of two columns in clickhouse

Sudhakar Selvaraj 0
Viewed 20

Is that possible to get id for maximum value of timestamp and duration. I am looking for query like

SELECT name, argMax(id, (timestamp, duration)) FROM tables GROUP BY name
clickhouse
1 Answers

It's unclear what you mean by the maximum.

Clickhouse is able to compare tuples from the left to the right

https://clickhouse.com/docs/en/sql-reference/data-types/tuple/

select (2022, 1, 1) > (2021, 12, 31);
┌─greater((2022, 1, 1), (2021, 12, 31))─┐
│                                     1 │
└───────────────────────────────────────┘

In this case you should use

SELECT name, argMax(id, (timestamp, duration)) 
FROM tables 
GROUP BY name

And Clickhouse has a function greatest https://clickhouse.com/docs/en/sql-reference/functions/other-functions/#greatesta-b

select greatest(2021, 2023);
┌─greatest(2021, 2023)─┐
│                 2023 │
└──────────────────────┘

Then you should use

SELECT name, argMax(id, greatest(timestamp, duration)) 
FROM tables 
GROUP BY name
Denny Crane0
Related