MySQL Greatest-N-Per-Group – Solving Multi-Table Groupwise Max Problem

greatest-n-per-groupMySQL

I have a query I'm having trouble with that involves 3 tables described below. There are more fields that are immaterial to this query.

users:
  id
  display_name
matches:
  id
  league_seasons_id
  date_played
  winner_id
  loser_id
players:
  id
  match_id
  player_id
  nth_match
  division
  result   # values are "Win" or "Loss"
  end_hcp

I'm trying to produce output like this:

  Name        Hcp   Wins   Losses   High Run    HR Pct of Hcp
  Alan        705    12       2       27            3.83%
  Bill        815    10       4       30            3.68%
  Charlie     611     9       5       12            1.96%
  Denise      788     8       6       15            1.90%
  ...

The query below mostly works, except for the Hcp column. In the players table there are 2 records for each match, one with the winner's info and one with the loser's. In each record is the start_hcp and the end_hcp, i.e. the handicap that player had before playing the match and the new one based on the match.

I need the end_hcp from the very last match that each player played up until now (the date/time of the query). I'm guessing there's going to be a subquery involved but I haven't figured out what it is.

SELECT 
    p.division AS Division,
    u.display_name AS Name,
    p.end_hcp AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "Loss", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM 
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE 
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses

Best Answer

I'm not a MySQL guy but what about something like the following (for end_hcp)?

Edit: based on syntax comment

SELECT 
    p.division AS Division,
    u.display_name AS Name,
    (select max(end_hcp) from lwljhb_lwl_players where player_id = p.player_id and match_id =(select max(p.match_id) from lwljhb_lwl_players where p.player_id = u.id))
 AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM 
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE 
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses
Related Question