Find average of last X records for every person

Question

I have a table which stores every score from every player. I want to take out the last 3 (or less) and display the average.

Example:

TABLE log
time  p_id  score
----  ----  -----
12:00 0     10
14:23 0     25
14:25 1     30
16:27 0     70
17:00 0     80

Now I want to find the average of all players' last 3 entries...

Result:

p_id  avg
----  ---
0     58
1     30

How should I proceed?

Answer 1

SELECT p_id, avg(score) as avg_score
FROM (
  SELECT p_id, score FROM log ORDER BY time LIMIT 3
)
GROUP BY p_id

should do the trick.