mysql取最大的五组,MySQL：获取组的最大值

I have a table:

user_id | fav_song_genre | votes_as_fav_member

--------+----------------+--------------------

1 | hip hop | 3

2 | hip hop | 5

3 | rock | 8

4 | rock | 6

How do I get only results of user_id's with the highest votes_as_fav_member per group fav_song_genre:

Something like

SELECT *, MAX(votes_as_fav_member) as most_votes

FROM table

GROUP BY

fav_song_genre

I'm using that but it's not giving me the ID's of the members with most votes per genre.

解决方案

Reasoning goes like

SELECT max vote for each genre

JOIN back with the original table to retrieve the additional columns for the records found.

SQL Statement

SELECT us.*

FROM UserSongs us

INNER JOIN (

SELECT fav_song_genre

, MAX(votes_as_fav_member) AS votes_as_fav_member

FROM UserSongs

GROUP BY

fav_song_genre

) usm ON usm.fav_song_genre = us.fav_song_genre

AND usm.votes_as_fav_member = us.votes_as_fav_member

Edit

How can I make sure the person with the lower ID is returned

SELECT MIN(us.user_id) as user_id

, us.fav_song_genre

, us.votes_as_fav_member

FROM UserSongs us

INNER JOIN (

SELECT fav_song_genre

, MAX(votes_as_fav_member) AS votes_as_fav_member

FROM UserSongs

GROUP BY

fav_song_genre

) usm ON usm.fav_song_genre = us.fav_song_genre

AND usm.votes_as_fav_member = us.votes_as_fav_member

GROUP BY

us.fav_song_genre

, votes_as_fav_member