SQLite best three times for men and women for each race distance

Question

I started with this simple query that gives me the three men with the best times (1st, 2nd and 3rd) on the 125km race.

SELECT *
FROM Coureurs
WHERE Genre=’M’ AND Epreuve='125km' AND TempsPassage IS NOT NULL
ORDER BY TempsPassage
LIMIT 3;

However, I would also need the three women (genre=F) with the best time on that distance. AND the best three men and three women for each of the other distances (TempsPassage=80km/65km/40km,..).

This is way past my level... and I really want to avoid building separate "hardcoded" queries. Thanks in advance, Pierre

Answer 1

You can use window function ROW_NUMBER() to rank the results for each race/genre and then filter to return only the rankings 1-3:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Epreuve, Genre ORDER BY TempsPassage) rn
  FROM Coureurs
  WHERE TempsPassage IS NOT NULL
)
WHERE rn <= 3
ORDER BY (Epreuve + 0), Genre, rn

If there is a case of ties then maybe try RANK() window function instead of ROW_NUMBER().