Select avg of top 5 in each group

Transact-SQL
1

I have a table
id,kidId,gameName,points,date
I want to get the avg points of the latest tries in each game for a specific kid?
I can get it for all recordes, but I dont know how to get the avg of the only the latest 5 records. (select top 5 ... order by date desc)
SELECT gameName,AVG(points) as [avg]
FROM records
where kidId=1
group by gameName

2

first you get the latest 5 and then avg .. hope this helps :slight_smile:

select avg from ( select latest 5 )

1 Like
3 
SELECT 
    kidId, gameName, AVG(points * 1.0) AS [avg] 
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY kidId, gameName ORDER BY date DESC) AS row_num
    FROM records
) AS derived
WHERE row_num BETWEEN 1 AND 5
GROUP BY kidId, gameName
ORDER BY kidId, gameName
2 Likes