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?

score 8 · Accepted Answer · answered Mar 19 '11 at 12:52

8

You need an aggregate function called max:

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

This way you will retrieve all distinct names, each one associated with its max rank.

answered Mar 19 '11 at 12:52

Pablo Santa Cruz

You still have to use `group by name` so that the `max(rank)` per `name` is returned, and not the overall `max(rank)`. – Dennis Benzinger Mar 19 '11 at 12:56
@Dennis: yes. already added that part. forgot with the rush to get the question to be the first one. :-) – Pablo Santa Cruz Mar 19 '11 at 13:01
4

This gives the rows where every name has the max rank, what you only wanted the name that had the max rank? – CMCDragonkai Jul 12 '16 at 07:17

score 7 · Answer 2 · answered Sep 12 '12 at 19:52

7

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

answered Sep 12 '12 at 19:52

Roger Russel

The syntax isn't correct(you can't `select *` whereas `group by name`) – Grisha Weintraub Sep 23 '12 at 06:58
3

I suppose that you didn't try to do this, because, if you had tried, you would known that it works. Not only on this example, but you can also get all information in the max row line. – Roger Russel Oct 30 '12 at 00:06
1

+1 You are right, I didn't know that `mysql` supports such a syntax. `MSSQL` and `Oracle` don't... – Grisha Weintraub Oct 30 '12 at 04:57

score 3 · Answer 3 · answered Mar 19 '11 at 12:54

3

Use max():

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

answered Mar 19 '11 at 12:54

Dennis Benzinger

3 Answers3