对同一请求使用mysql GROUP BY和ORDER BY

Question

I've been trying GROUP BY and ORDER BY in the same line to get what I want but it's not working. I am using a while loop that is running thousands of names, checking for highest points in each city. How do I get the name with highest points from each city, without repeating the same city twice?

This is what's in my database (in short):

ID City       Points    Name

1  NYC        16        Stan

2  London     24        Paul

3  NYC        11        Jeffrey

4  London     20        George

5  NYC        18        Ryan

$query = "SELECT `ID`, `City`, `Points`, `Name` FROM `table` GROUP BY `City` ORDER BY `Points`";

Gives me:

1 NYC 16 Stan

2 London 24 Paul

What I want it to give me:

2  London     24        Paul

5  NYC        18        Ryan

Answer 1

You can use left join as

select t1.* from table_name t1 
left join table_name t2 on t1.city=t2.city and t1.points < t2.points 
where t2.id is null;

Or using Uncorrelated subquery:

select t.* from table_name t 
join ( 
  select max(points) as points,city from table_name group by city
)x on x.city=t.city and x.points = t.points ;

Check the doc here https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Or Subquery

select t.* from table_name t 
where not exists (
  select 1 from test t1 where t.city = t1.city and t.points < t1.points
);