MySQL: "order by" inside of "group by"
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 BYname
ORDER BYrank
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 t开发者_如何转开发he 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
?
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.
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
Use max()
:
select name,
max(rank)
from names
group by name
order by max(rank) desc
精彩评论