CODEWITHSUNDEEP
mysqlsql
Pattle
Pattle

Reputation: 6016

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.

Upvotes: 2

Views: 86

Answers (1)

Jayvee
Jayvee

Reputation: 10875

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

Upvotes: 1

Related Questions