average of best 10

  • Thread starter Thread starter George
  • Start date Start date
G

George

Hi,
I have a data base of golf scores for a relative small
group, keeping track of the scores and dates of the
scores.
How can I extract with a query the average of the 10 best
scores from their last 20 games.

Thanks
George
 
My difficulty is not so much extracting the top 10 but
extracting the last 20 by dates and the dates may vary
for each entry.
Can anyone help?

George
 
Stacked queries, may be one way to do this.


Get the last 20 scores for each golfer

SELECT GolferID, Score, PlayDate
FROM Table
WHERE PlayDate IN
(SELECT TOP 20 T.PlayDate
FROM Table as T
WHERE T.GolferID = Table.GolferID
ORDER BY T.PlayDate DESC)

Get the top 10 Scores of that saved query, Saved as QueryOne

SELECT GolferID, Avg(Score) as AvgScore
FROM QueryOne
WHERE PlayDate IN
(SELECT TOP 10 PlayDate
FROM QueryOne as Q
WHERE Q.GolferID = QueryOne.GolferID
ORDER BY Q.Score, Q.PlayDate Desc)
GROUP BY GolferID

This assumes that they only play ONE game per date. If more than one game can
be played per day, then you will probably need some way to order uniquely.


SELECT
 
Back
Top