0

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
Nisse Engström
  • 4,738
  • 23
  • 27
  • 42
Alexander Vestling
  • 15
  • 5
  • You need to use `HAVING` and MAX(points). I can give you more info, if you prepare an sqlfiddle with sample data. – user4035 Mar 26 '15 at 18:46

2 Answers2

1

That's a groupwise maximum, one of the most commonly-asked SQL questions. You can try something like this,

SELECT tab1.*
FROM @Table AS tab1
LEFT JOIN @Table AS tab2 
     ON tab1.City=tab2.city  AND tab2.points > tab1.points
WHERE tab2.City IS NULL;
Community
  • 1
  • 1
Mahesh
  • 8,694
  • 2
  • 32
  • 53
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
);
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Thank you very much, Mr Chakraborty! I ended up using Uncorrelated subquery. Proved easier for me to alter. It worked like a charm:) – Alexander Vestling Mar 26 '15 at 19:25
  • But if I would like to apply a WHERE Gender='$male' it doesn't work for any of these three. How would I add WHERE to any of these? – Alexander Vestling Mar 26 '15 at 19:34
  • `where` should work , but it depends on what you are trying to achieve. Provide some sample data and desired result. – Abhik Chakraborty Mar 26 '15 at 19:37
  • $query = "select t.* from stad t join ( select max(points) as points,city from stad group by city )x on x.city=t.city and x.points = t.points where gender='$gender' "; – Alexander Vestling Mar 26 '15 at 19:43
  • Desired result is same as above but also checking for gender, sorting out females for example. – Alexander Vestling Mar 26 '15 at 19:45
  • Change the uncorrelated sub-query as `select max(points) as points,city from table_name where gender = 'Female' group by city` this will return all female data with max values – Abhik Chakraborty Mar 26 '15 at 19:51
  • If there is a same points available for same city with male and female then in that situation `(select max(points) as points,city,gender from table_name where gender = 'Female' group by city,gender)x on x.city=t.city and x.points=t.points and x.gender=t.gender` – Abhik Chakraborty Mar 26 '15 at 19:59
  • Wow that is exactly what I need, thank you so much! Your last answer was actually what I really needed to know. – Alexander Vestling Mar 26 '15 at 20:48
  • select max(points) as points,city from table_name where gender = 'Female' group by city. I'm using this line now since I don't want a same points available for same city but.... Im not getting the name and id, only points and city – Alexander Vestling Mar 27 '15 at 11:17
  • Post a different question. – Abhik Chakraborty Mar 27 '15 at 11:22