Selecting the top 3 records for a given value

  • Thread starter Thread starter joel_falk
  • Start date Start date
J

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?
 
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
..
 
Back
Top