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().

Answer 2

Making the following assumptions/changes

• That the Epreuve colum is numeric (store 125, 80 .... instead of 125km, 80km ....)

  • 125km and 80km if sorted will see 80km being greater than 125km
  • the text values will cost a little in additional storage
  • It is easy to extract the value with km appended if need be e.g. SELECT genre, Epreuve||'km', TempsPassage, Name FROM Coureurs

• The the TempsPassage column stores the time (example uses seconds but see Time Values)

Assuming the following data :-

Also assuming that you want a relatively simple to understand single query then the following could suit :-

WITH
     m125 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     f125 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     m80 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     f80 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     m65 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     f65 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     m40 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3),
     f40 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3)
SELECT * FROM m125 
    UNION SELECT * FROM f125
    UNION SELECT * FROM m80
    UNION SELECT * FROM f80
    UNION SELECT * FROM m65
    UNION SELECT * FROM f65
    UNION SELECT * FROM m40
    UNION SELECT * FROM f40
    ORDER BY Epreuve DESC,Genre DESC, TempsPassage ASC
;

This utilises what are called CTE's (Common Table Expressions), which are basically temporary tables.

• See The WITH Clause

One CTE is used per permutation of Genre and Epreuve (2 * 4 = 8) it is built with a similar query.

Once all the CTE's are created the 8 separate temp tables are combined using UNION's.

Using the data above the result is (obviously you may want to sort the results differently) :-

The following is the SQL used to test the above and create the results :-

DROP TABLE IF EXISTS Coureurs;
CREATE TABLE IF NOT EXISTS Coureurs (Genre TEXT, Epreuve int, TempsPassage int, name TEXT);
INSERT INTO Coureurs VALUES
    ('M',125,600,'Fred'),('M',125,610,'Bert'),('M',125,630,'Harry'),('M',125,620,'Albert'),('M',125,575,'David')
    
    ,('F',125,615,'Mary'),('F',125,625,'Anne'),('F',125,601,'Betty'),('F',125,625,'Sue'),('F',125,670,'Shelia')
    
    ,('F',80,450,'Louise'),('F',80,460,'Celia'),('F',80,425,'Debra'),('F',80,475,'Diana')
    
    ,('F',65,350,'Zara'),('F',65,360,'Yvonne'),('F',65,325,'Wilma'),('F',65,375,'Ursurla')
    
    ,('F',40,250,'Tracy'),('F',40,260,'Rhona'),('F',40,225,'Samantha'),('F',40,275,'Karen')
    
    ,('M',80,450,'Lou'),('M',80,460,'Colin'),('M',80,425,'Danny'),('M',80,475,'Eddy')
    
    ,('M',65,350,'Zed'),('M',65,360,'Mark'),('M',65,325,'William'),('M',65,375,'Tom')
    
    ,('M',40,250,'Jim'),('M',40,260,'Larry'),('M',40,225,'Peter'),('M',40,275,'Ronald')
;

SELECT * FROM Coureurs ORDER BY Random();
/* Easiest to understand - combining individual queries as CTE
    CTE = Common Table Expression (equates to temporary table) 
*/
WITH
     m125 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     f125 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 125 ORDER BY TempsPassage ASC LIMIT 3),
     m80 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     f80 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 80 ORDER BY TempsPassage ASC LIMIT 3),
     m65 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     f65 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 65 ORDER BY TempsPassage ASC LIMIT 3),
     m40 AS (SELECT * FROM Coureurs WHERE genre = 'M' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3),
     f40 AS (SELECT * FROM Coureurs WHERE genre = 'F' AND Epreuve = 40 ORDER BY TempsPassage ASC LIMIT 3)
SELECT * FROM m125 
    UNION SELECT * FROM f125
    UNION SELECT * FROM m80
    UNION SELECT * FROM f80
    UNION SELECT * FROM m65
    UNION SELECT * FROM f65
    UNION SELECT * FROM m40
    UNION SELECT * FROM f40
    ORDER BY Epreuve DESC,Genre DESC, TempsPassage ASC
;

DROP TABLE IF EXISTS Coureurs;