Grouping Question

[DBA-Meister]

I have a Scores table with 3 fields: ID, Week-Number, and Score.


This is for a double-elimination dart league, so each week can have any number of rows per ID, although it is rare for one person to have more than 7 scores in any one week.

We have a table for players that contains player name and player ID.

We want to calculate a handicap for each person as follows:
1. find the average of the highest scores for each person using only the 3 highest scores for that person (if he/she had 3 scores - might only be 2).
2. Calculate the average of the 20 most recent averages for each player.
3. Produce a report with Player Name and the calculated average.


I have two questions:
1. Can this be done in one single query or do we need to have queries calling queries?
2. Even if it can be done, does this make sense or should we use individual queries?

Thanks to anyone who can help.

[June7]

1. pull top 3 records for each person - review http://allenbrowne.com/subquery-01.html#TopN

2. would do you mean by '20 most recent' - last 20 weeks?

3. okay, get the queries built first

Probably queries calling queries (or nested).

[DBA-Meister]

I wasn't clear in the post. Each week a player shows up, he or she plays until they have lost 2 games. They will therefore have 2 or more entries (rows in the scores table) for each week they play. I want to find the 20 most recent weeks played for each player, find the average of their 3 highest games for each of those weeks played, then calculate the average of those averages for each players most recent 20 weeks. If a player hasn't yet played 20 weeks, I want to use all the weeks he or she has played. The scores table has 3 columns: Week_Nmbr, Player_id, Score.

[June7]

Sounds complicated. Never tried anything like this.

Maybe:

1. query that pulls TOP 3 for each week for each player

2. use that query in an aggregate query to calc the average

3. use that query in another query to pull TOP 20 weeks for each player

4. use that query in an aggregate query to calc average