Top 5 by Group query

[860Sharp]

I have a large file of athletic data. I have already made a query called "Rank by Pace by School" That has Sorted the data returning the fastest times per kid (no repeated kids) Per school. Table is called "Main Data"

File looks like (expr1 is the mile pace in secs)
School Name expr1
a Joe 357.7
a Jim 367.7
a John 378.7
a Fred 391.1
a Alan 395.7
a Frank 397.5
a Harry 398.7
a Bob 399.7
a Alan 400.2
b Alex 357.7
b Bill 367.7
....
I would like a query that takes the query above and only returns the top 5 results per school.
So for school a, it would only return results Joe through Alan. For school b returning only Alex through...whoever.

Can someone help me with this. I am trying to do it through design mode, but that may not be possible without going into SQL

 

[PHV]

Like this ?

SELECT A.School, A.Name, A.expr1
FROM [Rank by Pace by School] AS A INNER JOIN [Rank by Pace by School] AS B ON A.School=B.School AND A.expr1<=B.expr1
GROUP BY A.School, A.Name, A.expr1
HAVING Count(*)<=5
ORDER BY 1,3 DESC

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

 

[860Sharp]

It doesn't like
A.expr1<=B.expr1

 

[860Sharp]

Well it seems to be working. Only it is giving me the 5 slowest, not the 5 fastest

 

[PHV]

Sorry for the typo.
Replace this:
A.expr1<=B.expr1
with this:
A.expr1>=B.expr1

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

 

[860Sharp]

Follow up question. Is there a way to make it show only the teams that have 5 runners to show. Eliminating teams with only 1-2 athletes?