I have a comment table

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

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?

CodePudding user response：

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;