0

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 & 3
    2    | 0.713 - avg of ids 8,6 & 4
1

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 <= 3
group by yacht_id
order by yacht_id;

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

1
  • Thanks for the window function link rather than just providing an answer.
    – Col
    Oct 18 '18 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.