Get the average of the last X records for each foreign key My table is a list of yacht handicaps for yacht racing. After each race a new handicap is calculated based on how the yacht performed which is added to the table. The actual handicap used to modify the finish time of a yacht is the average of the last 3 races. I am having trouble getting my head around the sql to retrieve the race modifier of all yachts.

My table data (I have removed extraneous columns):

id | hcpval | yacht_id 
----------------------
 1 | 0.9    |  1
 2 | 0.75   |  2
 3 | 0.84   |  1
 4 | 0.71   |  2
 5 | 0.88   |  1
 6 | 0.73   |  2
 7 | 0.81   |  1
 8 | 0.7    |  2


Obviously, the most recent results have the higher id. After the query has finished I'm hoping to get the following results:

yacht_id | hcpval
-----------------
    1    | 0.843 - avg of ids 7,5 &amp; 3
    2    | 0.713 - avg of ids 8,6 &amp; 4
You need to find the three latest rows. That is typically done using a window function. The result of that can then be aggregated:

select yacht_id, 
       avg(hcpval) as hcpval
from (
  select yacht_id, 
         hcpval, 
         row_number() over (partition by yacht_id order by id desc) as rn
  from handicaps
)  t
where rn &lt;= 3
group by yacht_id
order by yacht_id;


Online example: https://rextester.com/DDTU17483