James Murrell
asked on
how to get fastest lap time and driver iD per race
hello as per title: how to get fastest lap time and driver iD per race
if i run
# raceId, driverId, lap, position, time, milliseconds
'841', '20', '1', '1', '1:38.109', '98109'
'841', '20', '2', '1', '1:33.006', '93006'
'843', '20', '3', '1', '1:32.713', '92713'
actual results in attached file
I would love result of
raceid, fastesttime, driverId
so i 10 races i would get 10 results if 700 races i would get 700 results..... hope that all makes sense and that you can help
result.csv
if i run
SELECT * FROM F1database.lapTimes
i get # raceId, driverId, lap, position, time, milliseconds
'841', '20', '1', '1', '1:38.109', '98109'
'841', '20', '2', '1', '1:33.006', '93006'
'843', '20', '3', '1', '1:32.713', '92713'
actual results in attached file
I would love result of
raceid, fastesttime, driverId
so i 10 races i would get 10 results if 700 races i would get 700 results..... hope that all makes sense and that you can help
result.csv
for this sample:
CREATE TABLE lapTimes
(`raceId` int, `driverId` int, `lap` int, `position` int, `time` varchar(12), `milliseconds` int)
;
INSERT INTO lapTimes
(`raceId`, `driverId`, `lap`, `position`, `time`, `milliseconds`)
VALUES
(841, 20, 1, 1, '1:38.109', 98109),
(841, 20, 2, 1, '1:33.006', 93006),
(843, 20, 3, 1, '1:32.713', 92713)
select
l.*
from lapTimes l
inner join (
select
raceId
, driverId
, min(milliseconds) milliseconds
from lapTimes
group by
raceId
, driverId
) m on l.raceId = m.raceId and l.driverId = m.driverId
and l.milliseconds = m.milliseconds
;
| raceId | driverId | lap | position | time | milliseconds |
|--------|----------|-----|----------|----------|--------------|
| 841 | 20 | 2 | 1 | 1:33.006 | 93006 |
| 843 | 20 | 3 | 1 | 1:32.713 | 92713 |
select
raceId
, driverId
, sum(milliseconds) / 1000 race_seconds
from lapTimes
group by
raceId
, driverId
;
| raceId | driverId | race_seconds |
|--------|----------|--------------|
| 841 | 20 | 191.115 |
| 843 | 20 | 92.713 |
select
`time`
, left(`time`,locate(':',`time`)-1) mins
, substr(`time`,locate(':',`time`)+1,2) secs
, right(`time`, length(`time`) - locate('.',`time`)) hundedths
, milliseconds / 1000
from lapTimes
| time | mins | secs | hundedths | milliseconds / 1000 |
|----------|------|------|-----------|---------------------|
| 1:38.109 | 1 | 38 | 109 | 98.109 |
| 1:33.006 | 1 | 33 | 006 | 93.006 |
| 1:32.713 | 1 | 32 | 713 | 92.713 |
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
darn, for fastest laptime one ignores the driverid.... oh well
Yep, I'm guessing we ignore the driverId, Paul. On the other hand, until we hear back from James we're just making our best guesses as to what he's really looking for. :-)
ASKER
wow thanks, sorry i had to sleep.
i get data from http://ergast.com/mrd/db/
I will review these later today. I am in meetings and will get back to you guys
i get data from http://ergast.com/mrd/db/
I will review these later today. I am in meetings and will get back to you guys
ASKER
Hello all great answer i will be accepting bcnagel answer as it it the one that makes sense to me: Basically I understand it....
big thanks guys
big thanks guys
ASKER
great answer, one i could read and understand.... thanks
are all the other columns int (integers)?
what version of MySQL are you using?