1

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?

0
0

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?

1
  • 1
    This will be inefficient. What is your MySQL Server version ? Please run SELECT Version(); query and report its result. Oct 9 '19 at 3:22
0

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

1
  • OVER requires 8.0
    – Rick James
    Oct 15 '19 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.