Hi all
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...
Any suggestions?
Thanks very much!
Rory
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