The following query works and gives me the desired results. The only problem is that it is very slow, and I suspect it is because of the multiple sub-queries included in it.
The table, RESULT, has multiple records per athlete, per stroke, per distance stored in the order of the events and not in rank order. I am pulling out the top time for each athlete for the selected stroke/distance/age/gender combination and ranking the result. ... In other words, I'm selecting the relevant swimmers' top times for a selected event and ranking those.
Variables are passed to the script - Selected Athlete_Id - 1234 - to generate age and gender for ranking - stroke_id and distance. These are already included in the code below.
The query returns 45 rows, which is correct, and, according to my SQLYog, it takes, on average, 14 seconds to process...
Thanks very much!
select a.ATHLETE, rtrim(a.LAST) as Surname, rtrim(a.FIRST) as FirstName, (select z.SCORE from RESULT z where z.ATHLETE = r.ATHLETE and z.STROKE = 1 and z.DISTANCE = 50 and z.SCORE > 0 and z.COURSE = 'L' order by z.SCORE limit 0,1) as SCORE, -- Selects top time for selected event & dist. (select m.MNAME from RESULT w Join MEET m on (w.MEET = m.MEET) where w.ATHLETE = r.ATHLETE and w.STROKE = 1 and w.DISTANCE = 50 and w.SCORE > 0 and w.COURSE = 'L' order by w.SCORE limit 0,1) as SWMEET -- Selects gala where the time was achieved from RESULT r Join ATHLETE a on (a.ATHLETE = r.ATHLETE) Join TEAM t on (t.TEAM = a.TEAM1) Join stroke s on (r.STROKE = s.stroke_id) WHERE a.AGE = (select AGE from ATHLETE where ATHLETE = 1234) and a.SEX = (select SEX from ATHLETE where ATHLETE = 1234) and r.COURSE = 'L' and r.STROKE = 1 and r.DISTANCE = 50 and r.SCORE > 0 and t.LSC = 'KZ' Group by r.ATHLETE Order by SCORE