toast toast - 2 months ago 13
MySQL Question

Find the rank of a user's lowest lap time

I have a number of race car drivers, that race at different tracks. I want to get their best lap time at each particular track, and rank it against other users best lap times at that particular track. I want to do this for all users at all tracks. So I want to return a large leaderboard.

Basically, if I have 10 race car drivers that have each completed 25 laps at track #1 I want to identify only the best lap time of each race car driver and rank those times against one another, at each track.

I think I ALMOST have it, see query below and a SQLFiddle link: http://sqlfiddle.com/#!9/64d6c/2

To make sure you have all the pieces, there is also a "tracks.verified" column. This determines whether or not the track has been verified. You don't really need to worry about this parameter and can leave it out if you want. I'm referring to this line:

AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)


http://sqlfiddle.com/#!9/64d6c/2

SELECT track_id, user_id, duration, @rank := @rank + 1 AS rank FROM
(
SELECT DISTINCT s1.track_id, s1.user_id, s1.duration
FROM session_lap_times s1
LEFT JOIN session_lap_times s2
ON s1.user_id=s2.user_id
AND s1.track_id = s2.track_id
AND s1.duration > s2.duration
WHERE s2.duration IS NULL
AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)
ORDER BY s1.duration ASC
) zz, (SELECT @rank := 0) z;


This outputs:

+----------+---------+------------------+------+
| track_id | user_id | duration | rank |
+----------+---------+------------------+------+
| 15 | 2 | 71001.5129995350 | 1 |
| 15 | 1 | 75001.5129995350 | 2 |
| 17 | 1 | 90258.1180334090 | 3 |
| 17 | 2 | 90897.0659971240 | 4 |
+----------+---------+------------------+------+


The problem is its ranking all against each other, and not against all by each track. These results should look like this:

+----------+---------+------------------+------+
| track_id | user_id | duration | rank |
+----------+---------+------------------+------+
| 15 | 2 | 71001.5129995350 | 1 |
| 15 | 1 | 75001.5129995350 | 2 |
| 17 | 1 | 90258.1180334090 | 1 |
| 17 | 2 | 90897.0659971240 | 2 |
+----------+---------+------------------+------+


I think this line is the problem:

AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)


Because if I put s1.track_id = 15 (for example), this works as expected for a single track. Just not them all.

Any help is appreciated!

EDIT:

I'm trying to filter the results by user_id. So I took your whole query and put it into a subquery. Is there any way to do this without putting it into a subquery? Thanks

select track_id, user_id, duration, rank from (SELECT track_id, user_id,duration,
CASE WHEN track_id!=@track THEN @rank:=0 END as reset,
@rank := @rank + 1 AS rank,
@track:=track_id
FROM
(
SELECT DISTINCT s1.track_id, s1.user_id, s1.duration
FROM session_lap_times s1
LEFT JOIN session_lap_times s2
ON s1.user_id=s2.user_id
AND s1.track_id = s2.track_id
AND s1.duration > s2.duration
WHERE s2.duration IS NULL
AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)
ORDER BY s1.duration ASC
) zz, (SELECT @rank := 0, @track := null) z) as x where user_id=1


As for counting the total number of drivers query I've got:

SELECT track_id, COUNT(user_id) FROM (SELECT track_id,user_id FROM session_lap_times GROUP BY track_id,user_id) as z GROUP BY track_id


Again, is there a nicer way of doing this without the subquery? Thanks! :)

Answer
SELECT track_id, user_id, duration, 
  CASE WHEN track_id!=@track THEN @rank:=0 END as reset,
  @rank := @rank + 1 AS rank,
  @track:=track_id
FROM
(
  SELECT DISTINCT s1.track_id, s1.user_id, s1.duration
  FROM session_lap_times s1
  LEFT JOIN session_lap_times s2
    ON s1.user_id=s2.user_id
   AND s1.track_id = s2.track_id
   AND s1.duration > s2.duration
  WHERE s2.duration IS NULL
    AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)
  ORDER BY s1.duration ASC
) zz, (SELECT @rank := 0, @track := null) z;

Tested with your fiddle.

Introduce an additional variable and reset rank on track_id change