MySQL: SUM Joined Table on Limited Selection of Rows

Question

I have a problem similar to this question but a bit more complicated and I'm having trouble figuring out how to do it efficiently. Given two tables, one for a list of athletes and one with a list of races they've run in, e.g.,

ATHLETES

• id
• name
• gender
• details

RACES

• athlete_id
• year
• points

I want to rank all of the athletes by gender for a given period of years using only their top 4 race finishes (where "top" is defined by points). I feel like I should be able to do this in a subquery but I can't figure out how to reference the outer query from the inner. What I have now looks like this:

SELECT SUM(points) as points, a.* FROM
(SELECT rr.points, inner_a.id as athlete_id 
FROM athletes_raceresult rr 
INNER JOIN athletes_athlete inner_a ON rr.athlete_id = inner_a.id
WHERE inner_a.gender ='m' AND rr.year BETWEEN 2012 AND 2014 
AND inner_a.id = a.id
ORDER BY rr.points DESC) as races 
INNER JOIN athletes_athlete a ON races.athlete_id = a.id
GROUP BY races.athlete_id 
ORDER BY points DESC

But that doesn't limit the points to 4 rows per athlete. It looks like I want a correlated subquery, but I can't get that to work.

Answer 1

The following SQL Fiddle example illustrates how this can be done:

SELECT SUM(rr.points), a.id 
FROM athletes_raceresult AS rr 
INNER JOIN athletes_athlete AS a ON rr.athlete_id = a.id
WHERE (
  SELECT count(crr.points)
  FROM athletes_raceresult AS crr 
  INNER JOIN athletes_athlete AS ca ON crr.athlete_id = ca.id
  WHERE ca.gender = 'm' 
  AND crr.year BETWEEN 2012 AND 2014 
  AND crr.athlete_id = a.id AND crr.points >= rr.points
) <= 4
AND a.gender = 'm' 
AND rr.year BETWEEN 2012 AND 2014 
GROUP BY a.id