MySQL: "order by" inside of "group by"

Question

I have a MySQL table of names, which consists of two fields: name and rank. The name value is not unique can have multiple matches.

The problem: I want to select records, grouped by name, but if there are more than one name, the one with the highest rank should be taken.

An example:

Tom 2

Ben 1

Ben 2

SELECT * FROM names GROUP BY name ORDER BY rank DESC

Usually returns:

Tom 2

Ben 1

I need:

Tom 2

Ben 2

Since there are two Bens, but the second one with a higher rank.

It seems, that MySQL grouping takes the first name and ignores the rest.

How do I order records inside "group by", so I could say which record should be taken, if there is more than one with the same name?

Answer 1

For me it has been worked:

To take the last row inside a group:

select * from 
( select name, rank from names order by rank desc ) as a 
group by name

Answer 2

Use max():

select name,
       max(rank)
from names
group by name
order by max(rank) desc