Find the highest value from a column for each unique value in another column with MySQL

I'm trying to write a MySQL query (from PHP) that will 开发者_C百科find the newest (highest ID number) score for each unique value in the group column.

I've tried a few different combinations of max() and GROUP BY but can't get it to work.

Here is an example of my table:

ID | my_group | score

1 | red | good
2 | blue | bad
3 | red | bad
4 | blue | good
5 | red | good
6 | yellow | bad
7 | blue | good
8 | blue | bad
9 | yellow | good
10 | blue | bad

So what I want to be returned from the above table would be:

ID | my_group | score

10 | blue | bad
9 | yellow | good
5 | red | good

---

select m.ID, m.my_group, m.score 
from (
    select my_group, max(ID) as MaxID
    from MyTable
    group by my_group
) mm
inner join MyTable m on mm.my_group = m.my_group
    and mm.MaxID = m.ID

---

Can't say this is guaranteed to work, but this simple technique worked in the limited testing I've done. Basically, I'm just reverse-sorting the table by ID, then selecting and grouping.

SELECT ID, my_group, score 
FROM (
    SELECT * FROM my_table ORDER BY ID DESC
) AS table
GROUP BY my_group