Top 3 best times for each distance

[VRunner]

Hi all

I've tried to follow the MS example in

http://support.microsoft.com/kb/210039

which works for just 2 tables, but I have more!

tblDistances / tblRaces / tblResults.
Each race has a single distance.
Each distance can apply to many races.
each race has many results.

So the objective is to find the top 3 results for each distance, and show the race name as a consequence. My query so far is as below which just returns the overall top 3 and not per group.

As always - help & guidance is appreciated.

SELECT tblRaces.RaceName, tblRaces.DistID, tblresults.ResTime, tblresults.ResName
FROM tblDistance INNER JOIN (tblRaces INNER JOIN tblresults ON tblRaces.RaceID = tblresults.RaceID) ON tblDistance.DistID = tblRaces.DistID
WHERE (((tblresults.ResTime) In (Select Top 3 [ResTime] From tblResults Where [tblRaces.DistID]=[tblDistance].[DistID] Order By [ResTime] Asc)))
ORDER BY tblRaces.DistID, tblresults.ResTime;

 

[PHV]

Change this:
Where [tblRaces.DistID]=[tblDistance].[DistID]
to this:
Where RaceID=tblRaces.RaceID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VRunner]

Hi,
thanks - I tried that before, but the query returns a list top 3 races/times, and not the distances/times.

i've uploaded the db as an example

http://www.runner-results.co.uk/test/test.zip

thanks.

 

[kjv1611]

If you're returning the wrong values, it's b/c you're looking for the wrong values in your SELECT Clause.

SELECT tblRaces.RaceName, tblRaces.DistID, tblresults.ResTime, tblresults.ResName

You said it's returning the races/times, and not the distances/times... so you need to adjust your SELECT Clause..

So, perhaps change that to this:

SELECT tblRaces.[highlight]Distance[/highlight], tblRaces.DistID, tblresults.ResTime, tblresults.ResName

Or else, please better explain what you're trying to do..

Perhaps, are you wanting to return the actual distance from a different table, instead of just the distanceID? If so, just substitute the correct table/field combination.

--

"If to err is human, then I must be some kind of human!" -Me