Select multiple columns but group by only 1 column

Question

I have a comment table

comment_id	when_added
10	02/23/2022
21	02/23/2022
10	02/24/2022

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

comment_id	when_added	count
10	02/24/2022	2
21	02/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 only using comment_id?

Answer 1

You should group only by comment_id and get the last when_added for each comment_id with MAX() aggregate function:

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