Complex query question?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy,

Given a table looking thusly:

Driver Truck EventClass Time Distance

Ed Sally Losers 14.334 0
Ed Sally Losers 0 189
Dave Trucky Losers 5.333 0
Phil Sally Losers 8.742 0
Dave Trucky Losers 0 242
Ed Trucky Winners 0 87
Ed Thingy Winners 12.14 0
Bert Sally Winners 7.964 0
Saul Thingy Losers 3.45 0
Gil Donkey Winners 6..55 0

What I need to see is an output based on the following: The Driver/Truck
combo is to be represented only once per EventClass, and the determing factor
is the lowest time, then the greatest distance, if the time is 0. So, for
the above list, Ed/Sally for Class Losers should only appear once with that
appearance being predicated on his time of 14.334. The list should look like
this when I'm done:

Driver Truck EventClass Time Distance

Saul Thingy Losers 3.45 0
Dave Trucky Losers 5.333 0
Phil Sally Losers 8.742 0
Ed Sally Losers 14.334 0
Gil Donkey Winners 6..55 0
Bert Sally Winners 7.964 0
Ed Thingy Winners 12.14 0
Ed Trucky Winners 0 87


Sorry if this is vague. I've been trying for hours and my mind is mush. I
greatly appreciate any help.
 
This **might** work for you.

SELECT DRIVER, TRUCK, EventClass, Time, Distance
FROM YourTable as T
WHERE IIF(Time=0,-Distance,Time) =
SELECT Min(IIF(Time=0,-Distance,Time))
FROM YourTable as T2
WHERE T2.Driver = T.Driver
AND T2.Truck = T.Truck
AND T2.Event = T.Event
GROUP BY Driver, Truck, EventClass)
 
Back
Top