Et en utilisant la fonction RANK() ?

Quelque chose comme :

create or replace view galette_athletics_speed_chal as
 select * from (
 select a.id_adh AS id_adh,
 re.id_race AS id_race,
 point_for_race(re.time,re.gender,ro.distance, NULL) AS points,
 point_bonus(re.category) as bonus,
 if((month(ra.race_date) < 9),(year(ra.race_date) - 1),year(ra.race_date)) AS saison,
 RANK() OVER (PARTITION BY a.id_adh ORDER BY re.id_race) AS rank
 from 
 galette_athletics_results re join galette_adherents a on a.id_adh = re.id_adh
 join galette_athletics_races ra on ra.id_race = re.id_race
 join galette_athletics_routes ro on ro.id_route = ra.id_route 
 where ro.is_neutralized = 0 and ro.is_challenge = 1 
 group by saison, a.id_adh, points, bonus, id_race
 ) AS t
 WHERE t.rank <= 6
 ORDER BY t.saison desc, t.id_adh, t.points desc;