title: Returning corresponding columns of a MAX function in MySQL
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>What I have so far is</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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)</p>