Clickhouse: Get last created_at row by client_id

[jonatasfreitasv]

Have any way to get lasted row (ordered by created_at) by client_id?

Table example:
created_at - client_id - event
2018-01-01 00:00:00 - 1 - start
2018-01-01 00:00:10 - 1 - finished
2018-01-01 00:00:05 - 1 - progress
2018-01-01 00:00:00 - 2 - finished
2018-01-01 00:00:10 - 2 - done

Expected result:
created_at - client_id - event
2018-01-01 00:00:10 - 1 - finished
2018-01-01 00:00:10 - 2 - done

I am using Distributed table with ReplicatedMergeTree.

[filimonov]

A lot of options...

SELECT
   client_id,
   argMax(event, created_at),
   max(created_at),
FROM table
GROUP BY client_id

-- or

SELECT 
client_id,
anyLast(event),
anyLast(created_at)
FROM
(SELECT client_id,event, created_at  FROM table ORDER BY created_at)
GROUP BY client_id

Also solutions with array functions possible.

Also if you don't need to store previous state (you need only last event always) - you can use CollapsingMergeTree or ReplacingMergeTree to 'overwrite' previous state all the time.

[jonatasfreitasv]

Man, you are a God!!! Thx a lot!!!