Searching by time and grouping by numer

[padstar]

HI guys... ive' got a queary here.. but i cant understand how to mod it to make it work correctly..

Select
*
From
lap_data
Where
lap_data.ld_lap_tm != ''
Order By
lap_data.ld_lap_tm


now the lapdata table is contains a bunch of records what are the output from a timing system..

the table contains about 260 records which relate to about 40 individual riders and i need to sort it in the order of the fasted (lowest timed lap) rider , the above works for that but i need to only return the row for each riders fastest lap, i try and group using the riders race number but i only get the fastest laps from the first instance of the riders in the table....

i know this as the number of the lap comes back at always 1 in the group but i know that some of the riders had better times on the 2nd and third lap....

please contact me if you need more info and data...
thanks for anyone's help in advance



pad

[SDas]

padstar, Something like this should work:

select name, lap_num, ld_lap_tme
from lap_data a
where ld_lap_tme = (select min(ld_lap_tme)
                    from lap_data b
                    where a.name = b.name)
  and ld_lap_tme <> ''

I used NAME as the column that identifies the Unique driver (since you didn't supply column names). You can substitue whatever you use.

[padstar]

thanks for the advice... now i'm being a bit daft here... and maybe this is part of my problem, i've been using flyspeed query tool to create and test my quearys, but if i create a queary like that flyspeed changes it and i only get one row of results...

Select
a.ld_rider_name,
a.ld_lap_num,
a.ld_lap_tm
From
lap_data a
Where
a.ld_lap_tm = (Select
Min(b.ld_lap_tm)
From
lap_data b
Where
a.ld_rider_name = b.ld_rider_name) And
a.ld_lap_tm <> ''

so whats going on... can you explain how this compound quearly actually works... it might be useful for me..

thanks

Pad

[SDas]

I made a small addition to the original query by adding AND B.LD_LAP_TM <> '' to the subquery.

Select a.ld_rider_name
     , a.ld_lap_num
     , a.ld_lap_tm
From lap_data a
Where a.ld_lap_tm = (Select Min(b.ld_lap_tm)
                     From lap_data b
                     Where a.ld_rider_name = b.ld_rider_name
                       And b.ld_lap_tm <> ''
                    ) 
  And a.ld_lap_tm <> ''

This query uses a correlated sub-query. The sub-query is correlated because is references A.LD_RIDER_NAME in the outside query.

How it work is:

Select the first row from the outside query. Using the LD_RIDER_NAME (ex. Jim), the sub-query is processed to return the MINIMUM ld_lap_tm for all rows where the name is JIM. This minimum time value is the used to compare to the time value in the outside query. If it matches you return a row.

If it does not match the next row is read. this riders name (ex. Jim again for a different lap) is passed to the sub-query to find the minimum time value. Eventually it the minimum time value for Jim will match the outside query and a row will be returned (note: more than one row could be returned in the same exact minimum time was recorded on two or more laps).

the process continues checking each person's name, and returning rows that match the minimum time value that that person's name.

PS This would also be written as a join.

Select a.ld_rider_name
     , a.ld_lap_num
     , a.ld_lap_tm
From lap_data a
       inner join
     (Select ld_rider_name, Min(b.ld_lap_tm)
      From lap_data
      where ld_lap_tm <> ''
      group by ld_rider_name
     ) as b
       on     a.ld_lap_tm = b.ld_lap_tm
          and a.ld_rider_name = b.ld_rider_name 
where a.ld_lap_tm <> ''

[padstar]

thanks for that... that's the perfect... I've got some other stuff where I'll need too search using a similar recursive patten... so I'll be trying to suss it out with my new found knowledge...

thank again

Pad