Search code examples
sqldatabaseclickhouse

Select only rows with max date

In clickhouse table I have multiple rows for one _id. What I want is to get only one row for each _id where column _status_set_at has its max value. That's what I am currently at:

SELECT _id, max(_status_set_at), count(_id)
FROM pikta.candidates_states
GROUP BY _id

output

Because I can't use max() function at WHERE clause, how to workaround this problem? count(_id) shows how many rows are there for each _id, if the query is correct, it should show 1. Also, as far as I concerned, there is no ON clause in Clickhouse database.

UPD: there is ON clause in Clickhouse

Solution

  • Your query returns what you need - only one row for each _id where column _status_set_at has its max value. You do not need to change anything in your original query.

    count(_id) shows how many rows for each _id in the original table, but not in a query result. Query result has only one row for each _id because you group by _id.

    This query shows that in your query result there is only one row for each _id

    SELECT _id, max_status_set_at, count(_id) FROM (
SELECT _id, max(_status_set_at) max_status_set_at
FROM pikta.candidates_states
GROUP BY _id) t
GROUP BY _id

    If you need apply a condition on max(_status_set_at) you can use HAVING