clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591627 fiddles created (45701 in the last week).

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


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');
 hidden batch(es)


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;
Epreuve # S Dossard TempsPassage
UTHC 65km 1 F 6008 2021-04-17 05:12:30
UTHC 65km 2 F 6007 2021-04-17 12:48:30
UTHC 65km 3 F 6006 2021-04-17 19:44:30
UTHC 65km 1 M 6002 2021-04-17 05:16:30
UTHC 65km 2 M 6005 2021-04-17 09:38:30
UTHC 65km 3 M 6001 2021-04-17 09:51:30
UTHC 80km 1 F 8010 2021-04-17 02:59:30
UTHC 80km 2 F 8007 2021-04-17 06:59:30
UTHC 80km 3 F 8011 2021-04-17 08:58:30
UTHC 80km 1 M 8003 2021-04-17 07:04:30
UTHC 80km 2 M 8004 2021-04-17 18:08:30
UTHC 80km 3 M 8006 2021-04-17 19:02:30
UTHC 125km 1 F 12 2021-04-17 07:37:30
UTHC 125km 2 F 13 2021-04-17 09:10:30
UTHC 125km 3 F 11 2021-04-17 18:24:30
UTHC 125km 1 M 4 2021-04-17 02:31:30
UTHC 125km 2 M 2 2021-04-17 05:27:30
UTHC 125km 3 M 3 2021-04-17 08:49:30
 hidden batch(es)