Hi i am new to this SQL stuff, as before have been able to the simple things in MS Access design view.
But i am having troubles acheiving what i need.
The situation is i have a database for horse racing stats.
There is a few tables, the ones that are related to this are
1. raceList - has raceId, date ... - a list of races
2. raceStarters - raceId, horseId ... - a list of horses in each race
3. previousForm - horseId, date etc...
what i want to be able to do is have a query that for a particular raceId, will display the last 5 previousForm records (sorted by date DESC), for each horseId, in that raceId.
thanks harshal_in, but it doesn't quite do what i wanted.
The query you have given returns only a total of 5 previousForm results for all horseId's.
What i need is that it will return 5 previousForm for each horseId. So that if there are 8 horseId in a raceId the query will return 40 previousForm results.
This is a shot in the dark (I haven't tried it), but:
FROM previousForm AS a
WHERE a.date IN (SELECT TOP 5 b.date
FROM previousForm AS b
WHERE b.horseId = a.horseId
AND EXISTS (SELECT *
FROM raceStarters AS c
WHERE c.horseId = b.horseId
AND c.raceId = @raceId)
ORDER BY b.date DESC)
The indentation is for a reason... The further right a line begins, the deeper it is logically nested. Start at the innermost stuff and work your way out and it shouldn't be too bad.
The SELECT aliased as c finds the horses that started in a given race. The SELECT aliased as b finds the most recent 5 dates for a given horse. The SELECT aliased as a is the one that actually produces the rows that go into the result set.