Selecting the top 3 records for a given value

[joel_falk]

I have a database that tracks running performances and I need to
select the top 3 running times for each runner at a given distance,
and create an average of those three.

I suspect I need to embed a select statement with the TOP command but
I am not sure how to return the average of the three performances, nor
how to do this for every runner all at once (if possible?).

Can someone please help?

 

[John Spencer]

Could you tell us some details on the design of your table(s)?

Assumption:
You have a single field Primary key

The following UNTESTED SQL MIGHT work

SELECT A.RunnerID
, Avg(A.Runtime) as AverageTime
FROM YourTable as A
WHERE A.PrimaryKey In (
SELECT Top 3 T.PrimaryKey
FROM YourTable as T
WHERE T.RunnerID = A.RunnerID
AND T.Distance= "100 Meter"
ORDER BY T.Runtime, T.PrimaryKey)
GROUP BY A.RunnerID

I suggest you build a TOP 3 query for one runner and one event and post the
SQL (Menu View:SQL). Along with some details on your field structure. That
way, someone may be able to suggest a detailed solution.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..