Select only rows with max date

Question

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

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

Teja · Answer 1 · 2018-08-16T14:20:58.610

2

Solution - 1 :

SELECT Z._id,
       Z._status_set_at
  FROM 
(
SELECT _id, 
       _status_set_at, 
       max(_status_set_at) OVER ( PARTITION BY _id ORDER BY _status_set_at DESC ) AS rnk
FROM pikta.candidates_states
) Z
WHERE Z.rnk = 1;

Solution - 2 :

SELECT A._id,
       A._status_set_at  
  FROM pikta.candidates_states A
CROSS JOIN
       ( 
         SELECT _id, 
                MAX(_status_set_at) AS max_status_set_dt         
           FROM pikta.candidates_states
         GROUP BY _id
        ) B
WHERE A._id = B._id
  AND A._status_set_at = B.max_status_set_dt;

edited Aug 16 '18 at 14:20

answered Aug 16 '18 at 14:00

Teja

13,214
36
93
155

2

I guess, partition and over is also not implemented in Clickhouse – Graygood Aug 16 '18 at 14:09
1

He says > there is no ON clause in Clickhouse database – Harun KARATAŞ Aug 16 '18 at 14:19
1

@Teja no. There is only ALL/ANY OUTER/INNER JOIN – Graygood Aug 16 '18 at 14:41
Second solution is pretty close to what I want, but some _id's still get multiple values. https://imgur.com/a/Ofm6rGq – Graygood Aug 16 '18 at 14:42
Is cross join returning any results or throwing an error? – Teja Aug 16 '18 at 14:42
@Teja ```Error running query: ('Connection aborted.', BadStatusLine("''",))``` – Graygood Aug 16 '18 at 14:44
@Teja found out, that multiple rows are returned because of some of its _status_id's could also repeat. Its clickhouse's problem, because it does'nt support millseconds. Thanks a lot – Graygood Aug 16 '18 at 15:04
Good. So its not the problem with cross join then. – Teja Aug 16 '18 at 15:05

score 2 · Answer 2 · answered Aug 16 '18 at 14:18

2

If you want to you your max clause on where statement maybe this will work

SELECT * from (SELECT _id, max(_status_set_at) as [MaxDate], count(_id) as [RepeatCount]
    FROM pikta.candidates_states
    GROUP BY _id) t WHERE t.MaxDate = '@parameter'

answered Aug 16 '18 at 14:18

Harun KARATAŞ

116
2
11

score 2 · Accepted Answer · answered Aug 17 '18 at 06:47

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

score 1 · Answer 4 · answered Nov 08 '21 at 14:24

1

in clickhouse，this will work

select _id, argMax(<col you need>, _status_set_at) from pikta.candidates_states group by _id;

argMax

answered Nov 08 '21 at 14:24

solosseason

11
1

sdrenn00 · Answer 5 · 2020-05-18T16:19:36.110

Replace <other columns> with list of other columns you need to select.

SELECT _id, _status_set_at, <other columns>
FROM pikta.candidates_states
WHERE (_id, _status_set_at) in (
    SELECT _id, max(_status_set_at)
    FROM pikta.candidates_states
    GROUP BY _id
)

Internal select returns pairs of _id with max _status_set_at for that _id. External select returns rows with additional colums from table, but only those of them, where _id and _status_set_at are in result of internal select, i.e. with max _status_set_at for each _id.

I posted my reply, because none of previous answers was good for initial question, as I understood it. Request in question should be able to return other colums, not only _id and _status_set_at, otherwise it's useless, you can use basic select for it, that already was mentiond in question. Mike's answer can't be modified that way. Solutions of Teja won't work in Clickhouse.

Select only rows with max date

5 Answers5