Pattle - 1 year ago 43

MySQL Question

I have two tables. A table of called

`laps`

`best_time`

I want to select from these tables the

`best_times`

`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`

`start_time`

`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 Source

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