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.

Hope this is clear and someone can help...

Thanks,
Blocka

I hope this is what u want:

select top 5 * from previousForm where horseid in (select horseid from racestarters where raceid=@raceid )
order by date desc



replace the @raceid with the actual raceid.


regards,
Harshal.

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.
Cheers,
Blocka..

This is a shot in the dark (I haven't tried it), but:-PatP

Thanks Pat that does indeed seem to work from a quick try. Now i just have to try and understand your query.
Thanks again.

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.

-PatP