MySQL Selecting details using max

Question

I have a set of MySQL tables containing information from a football management game.

The tables are:

• Players - playerID (PK), playerName
• Matches - matchID (PK), matchSeason, matchRound, matchType
• PlayersMatch - playerID, matchID (comp PK), matchRating, playerForm, playerAge, position (can be null)

Data stored in these tables are related to the performance of a player. A Player plays in a match and has a rated performance (matchRating). There is a record in PlayersMatch for each match a player participates in, recording the players current form, match performance, their age at the time of the match (for historical purposes) and the position they played in.

Now, currently, I'm using the following query to list the top 10 players from the whole season (best performance in the season overall as opposed to best performance per round):

SELECT playerID, matchID, playerForm, playerAge, MAX(matchRating)
FROM PlayersMatch
INNER JOIN Matches ON PlayersMatch.matchID = Matches.matchID
WHERE Matches.matchSeason = 35
AND Matches.matchType = 'L'
AND PlayersMatch.position IS NOT NULL
GROUP BY PlayersMatch.playerID
ORDER BY MAX(matchRating) DESC, playerForm ASC

The problem I'm getting is that while I'm getting the right playerID and player matchRating, I'm getting the wrong matchID, form, age and other info (ie they're from other records).

I tried adding the matchID to the group by, and while I got the correct information, I had duplicates as it produced duplicate records for the players (because playerID and matchID make up the PK in PlayersMatch).

Your assistance in this is greatly appreciated.

Edit: After some further reading, I must be writing the SQL incorrectly and that a group by is only going to return the correct information if I just have playerID and max(matchRating) - at the very least to be ANSI SQL correct.

In that case, how do I get the corresponding match details for that performance if I'm using a max/group by?

Edit 2: Looks like I've got a working query:

SELECT * FROM PlayersMatch
INNER JOIN 
   (SELECT playerID, MAX(matchRating)
    FROM PlayersMatch p2
    JOIN Matches
      ON p2.matchID = Matches.matchID
    WHERE matchSeason = 35
    AND matchType = 'L'
    AND p2.position IS NOT NULL
    GROUP BY p2.playerID) AS p1
  ON PlayersMatch.playerID = p1.playerID
  AND PlayersMatch.matchRating = p1.matchRating
JOIN Matches m2
  ON PlayersMatch.matchID = m2.matchID
WHERE m2.matchSeason = 35
AND m2.matchType = 'L'
AND PlayersMatch.position IS NOT NULL
ORDER BY matchRating DESC

The only problem now is that it takes 21 seconds to run. Does this query look correct?

Answer 1

Add a second index on PlayersMatch based on MatchID only for your preliminary qualification join to Matches. Add an index to your Matches table on matchSeason and type.

From your edit and posted data samples, I think this resolves to get the first "match" that qualifies the spanned multiple instances under a same "Rank". So, again, the inner-most gets the best MatchRating as your "MAX()" apparently is looking for the HIGHEST Rating. From that, it will immediately re-join to player matches and get the FIRST Match ID for that person with the same Rating. Finally, to close it out, we can directly join to the person for name info, and to the match based on the first match ID found, so no duplicates should be returned... That final result gets sorted per the match ranking..

SELECT STRAIGHT_JOIN
      Players.PlayerName,
      M2.*,
      PM.MatchRating,
      PM.PlayerForm,
      PM.PlayerAge,
      PM.Position
   FROM 
      ( select PreMatch.PlayerID,
               PreMatch.MaxMatch,
               MIN( P3.MatchID ) as FirstMatch
           FROM
               ( SELECT 
                    p2.playerID, 
                    MAX(p2.matchRating) MaxMatch
                 FROM 
                    Matches
                       JOIN PlayersMatch P2
                          ON Matches.MatchID = p2.matchID
                         AND P2.Position is not null
                 WHERE 
                        Matches.MatchSeason = 35
                    AND Matches.MatchType = 'L'
                 GROUP BY
                    p2.playerID ) PreMatch

               JOIN PlayersMatch P3
                   ON PreMatch.PlayerID = P3.PlayerID
                  AND PreMatch.MaxMatch = P3.MatchRating
                  AND P3.Position is not null

                  JOIN Matches M2
                     on P3.MatchID = M2.MatchID
                    AND M2.MatchSeason = 35
                    AND M2.MatchType = 'L' 
          GROUP BY
             PreMatch.PlayerID,
             PreMatch.MaxMatch
      ) AS p1

      JOIN Players
         on P1.PlayerID = Players.PlayerID

      JOIN PlayersMatch PM
          on p1.FirstMatch = PM.MatchID 

   ORDER BY 
      p1.MaxMatch DESC

Answer 2

An aggregate only works on the actual column it is applied. It is not record-based. It does select the max-value for the rating, but it does not determine how the other columns are aggregated.

So when you have records:

player 1 | match 1 | 10 
player 1 | match 2 | 5

and you group them by player, it needs to pick only one value for the 'match'-field, but which one is not defined: it doesn't depend on aggregates in other columns.

For what you want to do, you need a subquery:

SELECT p1.playerID, p1.matchID, p1.playerAge, MAX(p1.matchRating)
FROM PlayersMatch P1
JOIN PlayersMatch p2 on p1.id = 
   (SELECT id 
    FROM PlayerMatch p2 
    WHERE p2.playerId = p1.playerId 
    ORDER BY MAX(p2.matchRating) DESC 
    LIMIT 1)
GROUP BY playerID

Note that I've introduced a generated primary key for the playersmatch-table (as I'm not sure on the join-syntax for composite keys and general favor single-field artificial keys.) You can still have a unique constraint on (playerID, matchId).