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