Skip to main content
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
edited tags
Link
Taryn
  • 9.7k
  • 4
  • 46
  • 74
Source Link

select rows with min values of multiple columns

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?