dcsimg
Results 1 to 5 of 5

Thread: Searching by time and grouping by numer

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Smile Searching by time and grouping by numer

    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

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    padstar, Something like this should work:
    Code:
    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.

  3. #3
    Join Date
    Feb 2010
    Posts
    3

    Ta... but a question

    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

  4. #4
    Join Date
    Apr 2009
    Posts
    86
    I made a small addition to the original query by adding AND B.LD_LAP_TM <> '' to the subquery.
    Code:
    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.
    Code:
    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 <> ''

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •