Get top 2 records from sql

[Crocdundee]

Hi, I wish to retrieve the top 2 records for every Track and Race No from a query on a table
Here is the code to query the table
====================================
SELECT Selections.Rcdate, Selections.Track, Selections.Rcno, Selections.Rctime, Selections.Tab, Selections.Horse, Selections.Runners, Selections.Pred
FROM Selections
WHERE (((Selections.Rcdate)=[Forms]![FrmSelections]![Combo0]) AND ((Selections.Runners)>7))
ORDER BY Selections.Track, Selections.Rcno, Selections.Pred DESC;
==============================================================================
Rcdate picks a date filter from a form
Runners asks for only records where more than 7 runners in the race
Pred is the field that we get the top 2 from it is a numerical field where data is from 100 downwards
Hope one can help here
Regards
Graham

 

[pareshj]

Hi Crocdundee,


Check this query, i have tested it with dummy data, please add your criteria of rcddate and runners >7


SELECT s1.Track , s1.Pred
FROM Selections s1
WHERE (
SELECT COUNT(DISTINCT(s2.Pred))
FROM Selections s2
WHERE s2.Pred > s1.Pred and s1.Track = s2.Track )
in (0,1) group by s1.Track , s1.Pred