Limit grouping to 1 column when selecting multiple columns

Question

I have a comment form

comment_id	when_added
10	February 23, 2022
twenty one	February 23, 2022
10	February 24, 2022

I need to get the count, comment_id and the latest when_added

comment_id	when_added	count
10	February 24, 2022	2
twenty one	February 23, 2022	1

I tried this query

SELECT COUNT(*) as count, comment_id, when_added 
FROM comment 
GROUP BY comment_id, when_added 
ORDER BY when_added DESC;

Is there a way to group by just using comment_id?

Answer 1

You should only group by comment_id and use the MAX() aggregate function to get the last when_added of each comment_id ：

SELECT comment_id, 
       MAX(when_added) last_when_added, 
       COUNT(*) count
FROM comment 
GROUP BY comment_id 
ORDER BY last_when_added DESC;