I have a small database to keep track of weekly scores. Each participant can have 2 or more scores each week. Some players don't play every week and have no scores for those weeks. I have the following tables:
Person: pid, pname
Week: wn, CalDate
Scores: wn, pid, score
The scores table always has at least two scores [ rows ] for each player with any scores for that week, but not all players have scores every week.
I need to get the weekly average of the most recent three weeks for each player and then get the average of those three averages. Since some players don't play each week, an average for one player may come from weeks 4, 8, and 9, while another player has averages from weeks 8, 9, and 10.
I have a query that gets the averages of all the players for each week, and another query that gets the averages of those averages, but I haven't found a way to limit the first query to only the most recent three weeks.
I think the "top" function might help, but I've never used that before. Any help would be appreciated.
you get the three most recent weeks using OLAP functions:
select wn, pid, avg(score) as avgscore
group by wn, pid
qualify rank() over (partition by pid order by wn desc) <= 3
Thanks for the fast response. I still have a problem, though. When I run this query, I get this message:
Syntax error (missing operator) in query expriession
qualify rank() over (partition
) <= 3'
I used your exact statement. However, if I omit the last line, the query returns data without error, but it includes all the weeks, not just the most recent three.
I am running access version 14.0.4730.1000. Since this is a "single-user" database, I would think I don't need a more robust db engine.
You're using Access as database? Ouch.
Well, this is a Teradata forum and answers are based on Teradata's SQL dialect.
Access doesn't support QUALIFY or any OLAP function.
Using the limited SQL Access supports the only way to get that info is using a cursor sorted by pid and descending wn.