0

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.

Community
  • 1
  • 1
Tom
  • 22,301
  • 5
  • 63
  • 96

1 Answers1

2

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
Linger
  • 14,942
  • 23
  • 52
  • 79