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?