Need SubQuery Help - Table with Multi-Field PK

[bkimbrell]

I am trying to select the fastest times from a table Races where the PK
is made up on several fields such as:
Track
RaceDate
RaceNumber

The Races table contains fields that need to be grouped including:
Surface
Distance

And the field that I want to select is:
WinningTime - I believe that I want the Min(WinningTime)

The child table to Races is called Starts and has the same primary key
with one addition:
Track
RaceDate
RaceNumber
HorseName

I need to select the minimum WinningTime for each group of Surface &
Distance and then go to the starts table to get the HorseNames for each
of those combinations.

I see examples of using a Sub Query with a single primary key such as
the Northwind example of finding the max(OrderDate) for each customer
and then using that to get additional information, but I cannot get
this to work with my multi-field PK.

If I use the View - Totals option, I cannot specify RaceDate,
RaceNumber since this forces them to be "Grouped" and returns every
RaceDate and RaceNumber combination.

How do I find each unique Surface & Distance WinningTime while
returning information from the child table such as HorseName, RaceDate,
RaceNumber, etc?

 

[Ken Sheridan]

What you need to do is join the Races and Starts table on the keys in the
usual way and restrict the result set to those rows where the winning time
equals the result of a subquery which returns the fastest time and is
correlated with the outer query on the surface and distance columns. The two
instances of the races table are distinguished by giving each an alias, R1
and R2, which enables them to be correlated. A correlated subquery runs
independently for each row of the outer query, so only one row of each
surface/distance combination will be returned, i.e. the one with the fastest
winning time for that surface/distance:

SELECT R1.Track, R1.RaceDate, R1.RaceNumber,
R1.Surface, R1.Distance, R1.WinningTime,
Starts.HorseName
FROM Races As R1 INNER JOIN Starts
ON R1.Track = Starts.Track
AND R1.RaceDate = Starts.RaceDate
AND R1.RaceNumber = Starts.RaceNumber
WHERE R1.WinningTime =
(SELECT MIN(WinningTime)
FROM Races AS R2
WHERE R2.Surface = R1.Surface
AND R2.Distance = R1.Distance);

Ken Sheridan
Stafford, England

 

[bkimbrell]

Ken,

I tried your solution and it makes sense to me, but I get no items
returned. I have pasted my SQL in below.

Just a note, when I remove the subquery I do get a record for every
race and every horse that started after 8/1.

Here is what I am using:

SELECT R1.track_id, R1.race_date, R1.race_number, R1.country,
R1.official_indicator, R1.surface, R1.winning_time,
dbo_start.registration_number
FROM dbo_race AS R1 INNER JOIN dbo_start ON (R1.country =
dbo_start.country) AND (R1.day_evening = dbo_start.day_evening) AND
(R1.race_number = dbo_start.race_number) AND (R1.race_date =
dbo_start.race_date) AND (R1.track_id = dbo_start.track_id)
WHERE (((R1.track_id)="AP") AND ((R1.race_date)>#8/1/2006#) AND
((R1.country)="USA") AND ((R1.official_indicator)="Y") AND
((R1.winning_time)=(SELECT MIN(R2.winning_time) FROM dbo_race AS R2
WHERE R2.surface = R1.surface AND R2.distance_id = R1.distance_id)));

Any idea why this does not work?

Thanks in advance for your help.

Brad K.

 

[Ken Sheridan]

Brad:

As you are restricting the outer query on various fields you'll need to do
the same with the subquery to ensure that it only finds the fastest time from
those rows which would be returned by the outer query without the subquery.
If you omit to do this and the fastest times per surface/distance in the
table as a whole are not in the subsets of rows per surface/distance to which
the outer query is restricted then it won't return any rows.

Try this:

SELECT R1.track_id, R1.race_date, R1.race_number, R1.country,
R1.official_indicator, R1.surface, R1.winning_time,
dbo_start.registration_number
FROM dbo_race AS R1 INNER JOIN dbo_start
ON R1.country = dbo_start.country
AND R1.day_evening = dbo_start.day_evening
AND R1.race_number = dbo_start.race_number
AND R1.race_date = dbo_start.race_date
AND R1.track_id = dbo_start.track_id
WHERE R1.track_id="AP"
AND R1.race_date >#8/1/2006#
AND R1.country="USA"
AND R1.official_indicator="Y"
AND R1.winning_time =
(SELECT MIN(R2.winning_time)
FROM dbo_race AS R2
WHERE R2.surface = R1.surface
AND R2.distance_id = R1.distance_id
AND R2.track_id="AP"
AND R2.race_date >#8/1/2006#
AND R2.country="USA"
AND R2.official_indicator="Y");

Ken Sheridan
Stafford, England

 

[bkimbrell]

Ken,

Thank you very much. Your solution worked perfectly.

Brad K.