Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE race (Name VARCHAR(32), > Category VARCHAR(32), > totaltime INT, > s1time INT, > s2time INT, > s3time INT, > s4time INT) > > <pre> > ✓ > </pre> <!-- --> > 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) > > <pre> > ✓ > </pre> <!-- --> > SELECT * FROM race ORDER BY Category, Name > > <pre> > 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 > </pre> <!-- --> > 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 > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a73342e1185cefba777c22021b54965)*
back to fiddle