Manny Posted January 2, 2017 Share Posted January 2, 2017 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 More sharing options...
Manny Posted January 3, 2017 Author Share Posted January 3, 2017 For anybody browsing the forum and looking for an answer, I raised this same issue over at Stack and received some responses which solved my issue. http://stackoverflow.com/questions/41435315/order-by-maxfield-name-using-group-by Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now