CREATE TABLE [Coureurs] ( [Dossard] INTEGER PRIMARY KEY, [Prenom] NVARCHAR(25), [Nom] NVARCHAR(25), [Epreuve] NVARCHAR(10), [Sexe] NVARCHAR(1), [TempsPassage] DATETIME, [Statut] NVARCHAR(9));

INSERT INTO Coureurs VALUES (1,'Firstname','Lastname','UTHC 125km','M','2021-04-17 21:20:30','En_Course'), (2,'Firstname','Lastname','UTHC 125km','M','2021-04-17 05:27:30','En_Course'), (3,'Firstname','Lastname','UTHC 125km','M','2021-04-17 08:49:30','En_Course'), (4,'Firstname','Lastname','UTHC 125km','M','2021-04-17 02:31:30','En_Course'), (5,'Firstname','Lastname','UTHC 125km','M','2021-04-17 09:24:30','En_Course'), (9,'Firstname','Lastname','UTHC 125km','F','2021-04-17 23:25:30','En_Course'), (10,'Firstname','Lastname','UTHC 125km','F','2021-04-17 22:33:30','En_Course'), (11,'Firstname','Lastname','UTHC 125km','F','2021-04-17 18:24:30','En_Course'), (12,'Firstname','Lastname','UTHC 125km','F','2021-04-17 07:37:30','En_Course'), (13,'Firstname','Lastname','UTHC 125km','F','2021-04-17 09:10:30','En_Course'), (17,'Firstname','Lastname','UTHC 125km','M','','En_Course'), (18,'Firstname','Lastname','UTHC 125km','M','','En_Course'), (19,'Firstname','Lastname','UTHC 125km','M','','En_Course'), (22,'Firstname','Lastname','UTHC 125km','F','','En_Course'), (23,'Firstname','Lastname','UTHC 125km','F','','En_Course'), (24,'Firstname','Lastname','UTHC 125km','F','','En_Course'), (6001,'Firstname','Lastname','UTHC 65km','M','2021-04-17 09:51:30','En_Course'), (6002,'Firstname','Lastname','UTHC 65km','M','2021-04-17 05:16:30','En_Course'), (6003,'Firstname','Lastname','UTHC 65km','M','2021-04-18 00:18:30','En_Course'), (6004,'Firstname','Lastname','UTHC 65km','M','2021-04-18 00:46:30','En_Course'), (6005,'Firstname','Lastname','UTHC 65km','M','2021-04-17 09:38:30','En_Course'), (6006,'Firstname','Lastname','UTHC 65km','F','2021-04-17 19:44:30','En_Course'), (6007,'Firstname','Lastname','UTHC 65km','F','2021-04-17 12:48:30','En_Course'), (6008,'Firstname','Lastname','UTHC 65km','F','2021-04-17 05:12:30','En_Course'), (6009,'Firstname','Lastname','UTHC 65km','F','2021-04-17 22:16:30','En_Course'), (6010,'Firstname','Lastname','UTHC 65km','F','2021-04-18 03:12:30','En_Course'), (6011,'Firstname','Lastname','UTHC 65km','M','','En_Course'), (6012,'Firstname','Lastname','UTHC 65km','M','','En_Course'), (6013,'Firstname','Lastname','UTHC 65km','M','','En_Course'), (6014,'Firstname','Lastname','UTHC 65km','F','','En_Course'), (6015,'Firstname','Lastname','UTHC 65km','F','','En_Course'), (6016,'Firstname','Lastname','UTHC 65km','F','','En_Course'), (8002,'Firstname','Lastname','UTHC 80km','M','2021-04-17 23:52:30','En_Course'), (8003,'Firstname','Lastname','UTHC 80km','M','2021-04-17 07:04:30','En_Course'), (8004,'Firstname','Lastname','UTHC 80km','M','2021-04-17 18:08:30','En_Course'), (8005,'Firstname','Lastname','UTHC 80km','M','2021-04-17 19:11:30','En_Course'), (8006,'Firstname','Lastname','UTHC 80km','M','2021-04-17 19:02:30','En_Course'), (8007,'Firstname','Lastname','UTHC 80km','F','2021-04-17 06:59:30','En_Course'), (8008,'Firstname','Lastname','UTHC 80km','F','2021-04-17 17:26:30','En_Course'), (8009,'Firstname','Lastname','UTHC 80km','F','2021-04-18 00:30:30','En_Course'), (8010,'Firstname','Lastname','UTHC 80km','F','2021-04-17 02:59:30','En_Course'), (8011,'Firstname','Lastname','UTHC 80km','F','2021-04-17 08:58:30','En_Course'), (8012,'Firstname','Lastname','UTHC 80km','M','','En_Course'), (8013,'Firstname','Lastname','UTHC 80km','M','','En_Course'), (8014,'Firstname','Lastname','UTHC 80km','M','','En_Course'), (8015,'Firstname','Lastname','UTHC 80km','F','','En_Course'), (8016,'Firstname','Lastname','UTHC 80km','F','','En_Course'), (8017,'Firstname','Lastname','UTHC 80km','F','','En_Course');

SELECT Epreuve, rn AS "#", Sexe AS "S", Dossard, TempsPassage FROM ( SELECT *, ( SELECT COUNT(*) + 1 FROM Coureurs cc WHERE cc.Epreuve = c.Epreuve AND cc.Sexe = c.Sexe AND cc.TempsPassage <> '' AND cc.TempsPassage < c.TempsPassage ) rn FROM Coureurs c WHERE c.TempsPassage <> '' AND c.Statut = 'En_Course' ) WHERE rn <= 3 ORDER BY SUBSTR(Epreuve, 5) + 0 ASC, Sexe, rn;