Jump to content

ORDER BY MAX(`field_name`) Using GROUP BY


Manny

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...