Get the maximum average value for each individual record in SQL Query

Question

I have several tables containing data on the players and games that they played this season at the bowling center during the league. To do this, use a specific query: sort the upper X values ​​this year for men and women. I have it all, but I still have a problem in a particular case, when some players play in several leagues and have more than one of their average values ​​in the upper X.

Obviously, I only want to list the best average for this player, so if player A has the best average with 200 in the ABC League, as well as the second best average with 198 in the DEF League, I want only 200 of those listed.

Here is a simplified version of the query that I would like to change, because now I need to delete duplicates manually or I have to write the sorter in another language, but I would prefer to do it in pure SQL. (I removed the unnecessary information from the request for this example):

SELECT playerId, ROUND(AVG(score),2)Average, season, leagueName, COUNT(score)NumGames FROM Scores WHERE season = '2011-2012' AND score > -1 GROUP BY season, playerID, leagueName ORDER BY Average DESC LIMIT 0,30 

Basically, the Scores table contains each individual game, playerId, the season in which the game was played, and the league name (and other columns that are not required in this example).

WHERE must make sure that the game was played this season and that the rating is positive (-1 for those when people are absent). I group everything by season, playerID and leagueName, so I get the average PER LEAGUE for each player, and not the average of all games played in different leagues.

I tried to use the DISTINCT keyword, but this does not work because I cannot use DISTINCT for only one column. I also tried other things, but none of them came close to work, so I wonder if it is possible to do this or will I have to resort to using another language to sort this result set and remove duplicates?

Answer 1

You can calculate the average value for each player per league in a subquery:

 select playerId , max(league_avg.score) from ( select playerId , avg(score) as score from Scores where season = '2011-2012' and score > -1 group by playerId , leagueName ) as league_avg group by playerId 

Answer 2

OK, this is a bit of a challenge. I assume you can use the SELECT xxx INTO table to create a temporary table? In this case, these two choices will get what you want:

Firstly, I assume that your query above made a table called tmpscores.

Then you need to get the best results for each player:

 select playerID, MAX(average) AS bestscore INTO bestscores FROM tmpscores GROUP BY playerID, season 

Finally, take bestscores and re-join the pace to get the right league and number of games:

  SELECT bs.playerId, bs.bestscore, ts.season, ts.leaguename, ts.numgames FROM bestscores bs JOIN tmpscores ts ON bs.playerID = ts.playerId and bs.bestscore = ts.average 

There! Everything is in SQL.

Hope this helps!