clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798208 fiddles created (40921 in the last week).

CREATE TABLE race (Name VARCHAR(32), Category VARCHAR(32), totaltime INT, s1time INT, s2time INT, s3time INT, s4time INT)
 hidden batch(es)


INSERT INTO race (Name,Category,totaltime,s1time,s2time,s3time,s4time) VALUES ('Bob','Beginner',4000,1000,1000,1000,1000), ('Sally','Intermediate',4400,900,1200,1300,1100), ('Jack','Beginner',4500,1125,1075,1150,1150), ('John','Pro',4400,1100,1100,1100,1100), ('Charlie','Beginner',4400,1100,1100,1100,1100), ('Alice','Intermediate',4400,1100,1100,1100,1100), ('Paul','Beginner',4500,1125,1075,1150,1150), ('Fred','Pro',4400,1100,1100,1100,1100), ('Megan','Beginner',4400,1100,1100,1100,1100), ('Mike','Intermediate',4400,1100,1100,1100,1100)
 hidden batch(es)


SELECT * FROM race ORDER BY Category, Name
Name Category totaltime s1time s2time s3time s4time
Bob Beginner 4000 1000 1000 1000 1000
Charlie Beginner 4400 1100 1100 1100 1100
Jack Beginner 4500 1125 1075 1150 1150
Megan Beginner 4400 1100 1100 1100 1100
Paul Beginner 4500 1125 1075 1150 1150
Alice Intermediate 4400 1100 1100 1100 1100
Mike Intermediate 4400 1100 1100 1100 1100
Sally Intermediate 4400 900 1200 1300 1100
Fred Pro 4400 1100 1100 1100 1100
John Pro 4400 1100 1100 1100 1100
 hidden batch(es)


SELECT race.Category, GROUP_CONCAT(CASE WHEN race.totaltime = cte.totaltime THEN race.name END) total, GROUP_CONCAT(CASE WHEN race.s1time = cte.s1time THEN race.name END) s1, GROUP_CONCAT(CASE WHEN race.s1time = cte.s2time THEN race.name END) s2, GROUP_CONCAT(CASE WHEN race.s1time = cte.s3time THEN race.name END) s3, GROUP_CONCAT(CASE WHEN race.s1time = cte.s4time THEN race.name END) s4 FROM race, ( SELECT Category, MIN(totaltime) totaltime, MIN(s1time) s1time, MIN(s2time) s2time, MIN(s3time) s3time, MIN(s4time) s4time FROM race GROUP BY Category ) cte WHERE race.Category = cte.Category GROUP BY race.Category
Category total s1 s2 s3 s4
Beginner Bob Bob Bob Bob Bob
Intermediate Sally,Alice,Mike Sally Alice,Mike Alice,Mike Alice,Mike
Pro John,Fred John,Fred John,Fred John,Fred John,Fred
 hidden batch(es)