Another Select SQL issue

[crewchiefpro6]

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

http://www.crewchiefpro.com

 

[TamarGranor]

The TOP clause works only against the list as a whole, not against individual groups in the list. One way you could do this is to have a query pull out the list of sessions and then in a loop get the data for each session with another query.

Tamar

 

[crewchiefpro6]

Thanks Tamar, I was just hoping that I could combine this into one select statement.

For this weekend's race I had to kludge it like this:
LOCAL lnDriverCount, lcCarNumber

SELECT DIST carnumber ;
FROM csrAnswer ;
ORDER BY carnumber ;
INTO ARRAY carArray

lnDriverCount = _TALLY


CREATE CURSOR csrQualifying ( TIMESTAMP T(8), ;
SESSION C(40), ;
carclass C(20), ;
carnumber C(10), ;
drivername C(40), ;
iquarter N(10,3), ;
i1320mph N(10,3) )

FOR i=1 TO lnDriverCount
lcCarNumber = ALLTRIM(carArray)

SELECT TIMESTAMP, SESSION, carclass, carnumber, ;
drivername, MIN(iquarter), MAX(i1320mph) ;
FROM csrAnswer ;
WHERE ALLTRIM(carnumber) == lcCarNumber ;
INTO ARRAY QualifyingPositionArray

SELECT csrQualifying
APPEND FROM ARRAY QualifyingPositionArray

ENDFOR

SELECT * ;
FROM csrQualifying ;
ORDER BY iquarter, i1320mph ;
INTO CURSOR csrQualifyingBottom
***************************************

This works fine, and since the number of records is very low it is very fast.

Don




Don Higgins

http://www.crewchiefpro.com