Pattle - 4 months ago 16

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

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