select rows with min values of multiple columns

Question

I have been banging my head on this for a while...

I am using mysql to track results from a race - we have racers and categories (beginner, intermediate, Pro - for example) -- and each race has multiple timed segments, and a total time. The DB table looks as follows (not all columns shown)...

Name | Category | totaltime | s1time | s2time | s3time | s4time |
---------------------------------------------------------------------------------
Bob | Beginner | 4000.000 | 1000.000 | 1000.000 | 1000.000 | 1000.000 | 
Sally | Intermediate | 4400.000 | 900.000 | 1200.000 | 1300.000 | 1100.000 | 
Jack | Beginner | 4500.000 | 1125.000 | 1075.000 | 1150.000 | 1150.000 | 
John | Pro | 4400.000 | 1100.000 | 1100.000 | 1100.000 | 1100.000 | 
Charlie | Beginner | 4400.000 | 1100.000 | 1100.000 | 1100.000 | 1100.000 | 
Alice | Intermediate | 4400.000 | 1100.000 | 1100.000 | 1100.000 | 1100.000 |
Paul | Beginner | 4500.000 | 1125.000 | 1075.000 | 1150.000 | 1150.000 | 
Fred | Pro | 4400.000 | 1100.000 | 1100.000 | 1100.000 | 1100.000 | 
Megan | Beginner | 4400.000 | 1100.000 | 1100.000 | 1100.000 | 1100.000 | 
Mike | Intermediate | 4400.000 | 1100.000 | 1100.000 | 1100.000 | 1100.000 |

I am trying to pull the records with the fastest times on each segment for each category

Maybe something like

Category | Name | s1 | s2 | s3 | s4 |
------------------------------------------
Beginner | Jack | 1 | 0 | 0 | 0 | 
Beginner | Jack | 0 | 1 | 0 | 0 | 
Beginner | Paul | 0 | 0 | 1 | 0 | 
Beginner | Bob | 0 | 0 | 0 | 1 | 
Intermediate | Sally | 1 | 0 | 0 | 0 | 
Intermediate | Alice | 0 | 1 | 0 | 0 |
Intermediate | Mike | 0 | 0 | 1 | 0 |
Intermediate | Sally | 0 | 0 | 0 | 1 |
Pro | John | 1 | 0 | 0 | 0 |
Pro | John | 0 | 1 | 0 | 0 |
Pro | John | 0 | 0 | 1 | 0 |
Pro | Fed | 0 | 0 | 0 | 1 |

Or alternatively, maybe....

Segment | Category | Name |
----------------------------------------
1 |Beginner | Jack | 
1 |Intermediate | Sally | 
1 |Pro | John |
2 |Beginner | Jack | 
2 |Intermediate | Alice |
2 |Pro | John |
3 |Beginner | Paul |
3 |Intermediate | Mike |
3 |Pro | John |
4 |Beginner | Bob |
4 |Intermediate | Sally |
4 |Pro | Fred |

I have queres as follows that work for a single column

SELECT * FROM `raceresults` 
WHERE `s2time`=(SELECT min(`s2time`) FROM raceresults AS r WHERE `s2time`>0 AND r.`category`=`raceresults`.`category`)

But how to do this over multiple columns?

Answer 1

For MySQL version 8+

SELECT DISTINCT
 Category,
 FIRST_VALUE(Name) OVER (PARTITION BY Category ORDER BY totaltime ASC) total,
 FIRST_VALUE(Name) OVER (PARTITION BY Category ORDER BY s1time ASC) s1,
 FIRST_VALUE(Name) OVER (PARTITION BY Category ORDER BY s2time ASC) s2,
 FIRST_VALUE(Name) OVER (PARTITION BY Category ORDER BY s3time ASC) s3,
 FIRST_VALUE(Name) OVER (PARTITION BY Category ORDER BY s4time ASC) s4
FROM race

There is a problem - if 2 or more pilots have the same and least time for segment/whole then a random of them will be shown.

fiddle

And the solution applicable to any MySQL version, which shows ALL participants which have minimal time for segment/total:

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

If you need only one (random) participant then replace GROUP_CONCAT with, for example, MIN().

fiddle

Answer 2

I got the following to work...

SELECT
 r0.category,
 r0.name, 
 r1.s1time AS "s1",
 r2.s2time AS "s2",
 r3.s3time AS "s3",
 r4.s4time AS "s4",
 r5.s5time AS "s5"
FROM 
 (SELECT 
 name, riderid, category
 FROM 
 raceresults
 ) r0
 LEFT JOIN raceresults r1 
 ON r1.riderid = r0.riderid AND r1.s1time=(SELECT min(s1time) FROM raceresults WHERE s1time>0 AND category=r1.category)
 LEFT JOIN raceresults r2 
 ON r2.riderid = r0.riderid AND r2.s2time=(SELECT min(s2time) FROM raceresults WHERE s2time>0 AND category=r2.category)
 LEFT JOIN raceresults r3 
 ON r3.riderid = r0.riderid AND r3.s3time=(SELECT min(s3time) FROM raceresults WHERE s3time>0 AND category=r3.category)
 LEFT JOIN raceresults r4 
 ON r4.riderid = r0.riderid AND r4.s4time=(SELECT min(s4time) FROM raceresults WHERE s4time>0 AND category=r4.category)
 LEFT JOIN raceresults r5 
 ON r5.riderid = r0.riderid AND r5.s5time=(SELECT min(s5time) FROM raceresults WHERE s5time>0 AND category=r5.category)

Is there a better way?

• Madhur Bhaiya: This will be inefficient. What is your MySQL Server version ? Please run SELECT Version(); query and report its result.