Mysql – select rows with min values of multiple columns

MySQLquery

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?

Best Answer

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?

Related Question