Returning corresponding columns of a MAX function in MySQL

Question

I have a table that contains the scores bowled by players in a bowling center. Each row has some data about which player bowled the game, which league it was played in, the date, the score of a single game, the lane number, etc.

What I'm trying to do is get who played (and in which league and on what date... Basically the whole row) the best series (three games) on every single lane.

What I have so far is

SELECT PlayerID, LaneNumber, MAX(Series)
  FROM (SELECT Season, LeagueName, LaneNumber, WeekNumber, PlayerID, Date, SUM(Score) AS Series 
          FROM Scores
      GROUP BY Season, LeagueName, WeekNumber, PlayerID)
GROUP BY LaneNumber

This works, as in I get the best three games for every single lane, which is actually what I want, but the other field containing the PlayerID isn't actually correct.

In my table, the best score on lane number 24 (gotten from the SUM(Score) and GROUP BY Season, LeagueName, WeekNumber, PlayerID) is 848 and was played by the player that has PlayerID 36.

What I get instead is Lane 24 with 848 (which is correct), but the PlayedID returned is 3166. The same thing happens on every single lane. (As in, I get PlayerIDs that are plain out wrong. And if I had other columns in the first select, they're also wrong)

Answer 1

You are violating the semantics of GROUP BY.

When using GROUP BY, it's only meaningful to SELECT columns that you have grouped by (e.g. LaneNumber) and aggregate functions of the other columns (e.g. MAX(Series)). It is not meaningful to select anything else (in your case, PlayerID) because you don't specify which player ID you want among those that share the same LaneNumber.

Sadly, MySql will by default let you do this without reporting an error, and it will return any value it chooses for the offending column. In your case, this means you are getting back a player ID "randomly" chosen among those that are included in the specified grouping.

You are also doing this in the inner query, where you select LaneNumber, WeekNumber and Date.

Solution

The query needs to be rewritten, but first you need to carefully specify exactly which results you want to get. Do you want the best player and relevant data for each series (and any lane)? For each series and lane separately? The answer to this question will dictate what you need to GROUP BY, and by extension what the query will look like.