Pattle Pattle - 2 months ago 8
MySQL Question

MySQL select row where one field is less than the previous record and one field is greater

I have two tables. A table of called

laps
which holds a record of all laps completed round a track by a car and a table called
best_time
that consists of fastest times for certain distances on that lap. For example it will contain the fastest 1k in that lap, or the fastest half mile.

I want to select from these tables the fastest time progression for each distance. So it will show your personal progression for that distance on that lap over time. E.g your fastest 1k might have been set in January and then you broke it in June and again in August. Below is what the
best_times
table structure looks like

best_time_id int(10)
lap_id int(11)
start_time int(10)
end_time int(10)
total_distance decimal(7,2)
total_elapsed_time decimal(11,2)


I need to select records where the
total_elapsed_time
is less than the previous record and the
start_time
is greater. Here is my query so far

SELECT `bt`.`total_distance`, `bt`.`total_elapsed_time`, `bt`.`start_time`
FROM `best_times` AS `bt`, `laps` AS `l`
WHERE (
SELECT COUNT(*) FROM `best_times` AS `bt2`
WHERE `bt2`.`total_distance` = `bt`.`total_distance`
AND `bt2`.`total_elapsed_time` <= `bt`.`total_elapsed_time`
AND `bt2`.`start_time` > `bt`.`start_time`
) <= 10 AND `l`.`lap_id` = `bt`.`lap_id` AND `l`.`car_id` = 1 ORDER BY `bt`.`total_distance` ASC, `bt`.`total_elapsed_time` desc


This kind of works but it selects record that is shouldn't. An example of a result set I'm getting back is this

| total_distance | total_elapsed_time | start_time |
|----------------|--------------------|------------|
| 1000.00 | 99.15 | 1431344798 |
| 1000.00 | 98.25 | 1431604966 | This record shouldn't be here because although it's quicker it happened after the 91.40 time
| 1000.00 | 91.40 | 1431433535 |


I'm close but can anyone see where I'm going wrong. Please let me know if I need to provide more information.

Answer

This will show all the rows where elapsed time is less than all previous elapsed times, by total distance and lap id for car=1:

SELECT `bt`.`total_distance`, `bt`.`total_elapsed_time`, `bt`.`start_time` 
FROM `best_times` AS `bt`, `laps` AS `l` 
WHERE `bt`.`total_elapsed_time` <= (Select min(`bt2`.`total_elapsed_time`) from `best_times` AS `bt2` where `bt2`.`start_time` <= `bt`.`start_time` 
AND `bt2`.`total_distance` = `bt`.`total_distance`  )
AND `l`.`lap_id` = `bt`.`lap_id` 
AND `l`.`car_id` = 1 
ORDER BY `bt`.`total_distance` ASC, `bt`.`total_elapsed_time` desc

I'm not too sure about what is lap_id, maybe it can be removed