GoodyGoody
Registered User.
- Local time
- Today, 14:42
- Joined
- Aug 31, 2019
- Messages
- 120
Hi Guys, I want to calculate the top 'N' results of each runner within a series of races. I have the following SQL which nicely generates the valid runners (those who have run the requisite number of races in the series):
SELECT Q.SeriesName, Q.DateFrom, Q.DateTo, Q.RaceRunner, Q.RaceTime, Q.RaceRunnerNumber, Q.RacePosition, Q.RaceCategory, Q.RaceGender, Q.RaceGenderPosition, Q.RaceRunnerFullName, Q.[Club Name], Q.HowManyRaces
FROM qryRaceSeriesResults AS Q INNER JOIN (select RaceRunner, count(*) AS [QualifyingRaces] from qryRaceSeriesResults
GROUP BY Racerunner) AS R ON Q.RaceRunner = R.RaceRunner
WHERE R.QualifyingRaces >= Q.HowmanyRaces;
Of course runners may have run more than the number of qualifying races so I only want to include the top 'N' results FOR EACH RUNNER not for the whole recordset. If I just use SELECT TOP 'N' I just the get the overall TOP 'N' results but I want the TOP 'N' for EACH runner in the recordset. Can I do this in SQL or do I have to resort to VBA?
Thanks as ever for all your great help. I am using Access 2013.
Stephen
SELECT Q.SeriesName, Q.DateFrom, Q.DateTo, Q.RaceRunner, Q.RaceTime, Q.RaceRunnerNumber, Q.RacePosition, Q.RaceCategory, Q.RaceGender, Q.RaceGenderPosition, Q.RaceRunnerFullName, Q.[Club Name], Q.HowManyRaces
FROM qryRaceSeriesResults AS Q INNER JOIN (select RaceRunner, count(*) AS [QualifyingRaces] from qryRaceSeriesResults
GROUP BY Racerunner) AS R ON Q.RaceRunner = R.RaceRunner
WHERE R.QualifyingRaces >= Q.HowmanyRaces;
Of course runners may have run more than the number of qualifying races so I only want to include the top 'N' results FOR EACH RUNNER not for the whole recordset. If I just use SELECT TOP 'N' I just the get the overall TOP 'N' results but I want the TOP 'N' for EACH runner in the recordset. Can I do this in SQL or do I have to resort to VBA?
Thanks as ever for all your great help. I am using Access 2013.
Stephen