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

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?

Answer 2

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