Get max average for each distinct record in a SQL

Question

I have some tables that contain data about players and the games they have bowled this season in a bowling center during their leagues. What this particular query is used for is to sort the top X averages this year for men and for women. I have all of this down, but I still have a problem in some particular case when some players play in multiple leagues and have more than one of their averages in the top X.

Obviously, I only want to list the best average for a given player, so if Player A has the best average with 200 in League ABC and also the second best average with 198 in League DEF, I only want the 200 listed.

Here's a simplified version of the query that I would like to change, because right now I have to remove the duplicates manually or I would have to write a sorter in another language, but I'd rather do it in pure SQL. (I only removed irrelevant information from the query 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, a playerId, the season in which the game was played and the leagueName (and other columns that are not required in this example).

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

I tried using the DISTINCT keyword, but that doesn't work because I can't use DISTINCT for only a single column. I also tried other things but none of them came even close to working, so I'm wondering if it's even possible to do this or if I'll have to resort to using another language to sort this result set and removing duplicates?

Answer 1

You could calculate the average per 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'm assuming you can use SELECT xxx INTO table to generate a temp table? In that case, these two selects will get you what you want:

First, I'm assuming your query above made a table called tmpscores.

Then you need to get, for each player, the BEST scores:

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

Finally, take the bestscores and re-join the temp scores to get the proper 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! All in SQL.

Hope it helps!