ORDER BY MAX(`field_name`) Using GROUP BY

Recommended Posts

Hey guys - Happy New Year to all.

I'm currently working on a soccer website. A feature I'm developing goes back through history to see which player(s) scored the most goals in each year. I've found a solution, but think there should be a simpler way of going about it.

Here is some example data:

```-----------------------
year | name    | goals
-----------------------
1  | player1 |   6
1  | player2 |   8
1  | player3 |   4
2  | player1 |   3
2  | player2 |   5
2  | player3 |   2
3  | player1 |   7
3  | player2 |   7
3  | player3 |   3
```

What I want to get from a query is the value of 'name' and 'goals', using GROUP BY 'year', but only for the maximum value of 'goals' for each year.

Where I run into trouble is in an instance where there are two 'name' fields with the same number of 'goals' - for instance in 'year' 3, where 'player1' and 'player2' both have 7 goals.

I know I can make use of the GROUP_CONCAT feature, but am unsure how to go about getting the following results:

```-------------------------------
year | name            | goals
-------------------------------
1  | player2         | 8
2  | player2         | 5
3  | player1,player2 | 7
```

To get those results, I'm currently using two queries. Basically, I'm finding the value of MAX(`goals`) using GROUP BY `year`. Then, by looping through the results, I'm searching for 'name' separately by using WHERE clauses for both 'year' and 'goals'.

```SELECT `year`, MAX(`goals`) FROM `table` GROUP BY `year`

LOOP RESULTS

SELECT `year`, GROUP_CONCAT(`name`), `goals` FROM `table` WHERE `year` = :year AND `goals` = :goals
```

Finding the value of 'year' and 'goals' is relatively simple, but I can't find a way to find GROUP_CONCAT(`name`) without resorting to using a second query within a loop.

Any help combining the two would be very much welcomed as it would help save time with similar queries later in my project.