How do you get the id values after running an aggregate function-CodePudding

given a comment table that looks like

| id | post_id | reply_count
| 1  | 1       | 10
| 2  | 2       | 5
| 3  | 1       | 20

I'm trying to return the comments, with its id, with the most reply counts, so I'd like the query to return rows with id 2 and 3.

| id | post_id | reply_count
| 2  | 2       | 5
| 3  | 1       | 20

If I try running

SELECT id, max(reply_count) as reply_count, post_id
From Comment
GROUP BY post_id

I get SELECT list is not in GROUP BY clause and contains nonaggregated column. I understand that this is because multiple ids can be returned if multiple post_ids have the same reply_count, but is there a way to have it just return the min(id) in these cases?

I've seen the suggestion to change the sql mode and I'm wondering if there's a way to get the results I'm after without doing that

mysql version: 8.0.25

CodePudding user response：

in mysql 8 and above :

select * from (
   select * , row_number() over (partition by post_id order by reply_count desc) rn
   from tablename
) t where rn = 1