MySQL: "arrange" inside a "group"

Question

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

Problem: I want to select records grouped by name , but if there are more than one name , I should accept the value with the highest rank .

Example:

Volume 2

Ben 1

Ben 2

SELECT * FROM names GROUP BY name ORDER BY rank DESC

Usually returns:

Volume 2

Ben 1

I need:

Volume 2

Ben 2

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

The MySQL group seems to take a name and ignore the rest.

How can I order entries inside "group by", so I can say which entry should be made if there is more than one with the same name ?

Answer 1

You need one called max :

 select name, max(rank) from names group by name; 

This way you get all the different names, each of which is associated with its maximum rank.

Answer 2

Use max() :

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

Answer 3

For me it worked:

To take the last line inside a group:

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