Select SQL Problem

I am trying to select records from a database to determine qualifying order over 3 rounds of competition.  I *thought* I had it working but the results are not always correct.

I need to find the lowest number in the field IQUARTER from a list of runs from many different entries listed by the field RACESESSIONS, but I need only the lowest for each contestant.  If there is a tie then the highest Miles Per Hour from the field I1320MPH breaks the tie, if there is an additional tie then the first one is placed above the other, this is determined by the TIMESTAMP field.

The structure of the database that matters is this:
timestamp (datetime)
RaceSession (character)
CarNumber (character)
iQuarter (numeric 10,3) *Most Important*
i1320MPH (numeric 10,3)

the following records are an example, I CAN post an example with actual data and a small PRG file to test if necessary.
(timestamp) , (racesession) , (carnumber), (iquarter) , (i1320mph)
10/22/2005 09:30, TOP FUEL Q1, 3, 4.599, 321.50
10/22/2005 09:30, TOP FUEL Q1, 1, 4.573, 324.21
10/22/2005 09:31, TOP FUEL Q1, 6, 4.509, 329.63
10/22/2005 09:31, TOP FUEL Q1, 2, 4.509, 328.55
10/22/2005 09:32, TOP FUEL Q1, 9, 4.563, 325.15
10/22/2005 09:32, TOP FUEL Q1, 8, 4.600, 309.45
10/22/2005 09:33, TOP FUEL Q1, 7, 4.598, 316.33
10/22/2005 09:33, TOP FUEL Q1, 5, 4.523, 327.54
***
10/22/2005 2:30, TOP FUEL Q2, 3, 4.579, 321.50
10/22/2005 2:30, TOP FUEL Q2, 1, 4.543, 324.21
10/22/2005 2:31, TOP FUEL Q2, 6, 4.499, 333.63
10/22/2005 2:31, TOP FUEL Q2, 2, 4.489, 334.55
10/22/2005 2:32, TOP FUEL Q2, 9, 4.513, 325.15
10/22/2005 2:32, TOP FUEL Q2, 8, 4.500, 309.45
10/22/2005 2:33, TOP FUEL Q2, 7, 4.588, 316.33
10/22/2005 2:33, TOP FUEL Q2, 5, 4.502, 327.54
**
10/23/2005 09:30, TOP FUEL Q3, 3, 4.759, 321.50
10/23/2005 09:30, TOP FUEL Q, 1, 4.633, 324.21
10/23/2005 09:31, TOP FUEL Q3, 6, 4.59, 329.63
10/23/2005 09:31, TOP FUEL Q3, 2, 4.579, 328.55
10/23/2005 09:32, TOP FUEL Q3, 9, 4.593, 325.15
10/23/2005 09:32, TOP FUEL Q3, 8, 4.700, 309.45
10/23/2005 09:33, TOP FUEL Q3, 7, 4.908, 316.33
10/23/2005 09:33, TOP FUEL Q3, 5, 4.623, 327.54
*** end of data example

The Proper Results from the data above would be the following qualifying positions:
Position  * CarNumber * iQuarter
#1  *  2   *   4.489
#2  *  6   *   4.499
#3  *  8   *   4.500
#1  *  5   *   4.502
#1  *  9   *   4.513
#1  *  1   *   4.543
#1  *  3   *   4.579
#1  *  7   *   4.581

What I do first is construct a select statement using an "IN" clause that works great, it selects all the cars in the first 3 sessions and the list of data appears as above with each session grouped together.  This select looks like this:

local lcAnswer
** construct the IN clause for the where part of the select statement

For example the variable lcAnswer may look like this:

lcAnswer=["TOP FUEL Q1","TOP FUEL Q2","TOP FUEL Q3"]
lcAnswer="("+ (lcAnswer) + ")"

SELECT * ;
    FROM icard ;
    WHERE RACESESSION IN &lcAnswer ;
    .AND. iquarter <> 0 ;
    ORDER BY iquarter, i1320mph, SESSION ;
    INTO CURSOR csrAnswer
This cursor works fine, and displays the info as in the example perfectly.

Next I need to find a way to select out the top 32 drivers in the order of the lowest iQuarter then i1320mph.

I use this command currently which gives inconsistant results.

SELECT TOP 32 csrAnswer.ioverunder, csrAnswer.carnumber, csrAnswer.TIMESTAMP,    csrAnswer.RACESESSION, ;
    csrAnswer.carclass, csrAnswer.drivername, ;
    csrAnswer.ilane, csrAnswer.idialin, csrAnswer.ireaction, ;
    csrAnswer.i60foot, csrAnswer.i330foot, csrAnswer.i660foot, ;
    csrAnswer.i660mph, csrAnswer.i1000foot, csrAnswer.iquarter, ;
    csrAnswer.i1320mph, csrAnswer.imargin, csrAnswer.iwin ;
    FROM csrAnswer ;
    GROUP BY csrAnswer.carnumber ;
    ORDER BY csrAnswer.iquarter, csrAnswer.i1320mph ;
    INTO CURSOR csrQualifyingTop

This works fine if there is only one session, as soon as I include the other sessions in the original select then every pass they make appears and the order is not correct.

I am certain it can be done with one select statement but 2 is fine to verify the correct information.  Any ideas

Don

Don Higgins
www.crewchiefpro.com

Answer this question