Get the average of the last X records for each foreign key
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