How to take particular number for each group

April 17, 2022

I have the following table sport statistic table:

I would like 10 last records from the table for each team and then analyze and aggregate them.

Totally, I have 12 records for each team. But I want to analyze 10 only. I have the following SQL query

select
       guest_team_id,
       home_team_id,
       count(drawn)          drawn_count,
       count(home_team_lost) home_team_lost_count,
       count(home_team_won)  home_team_won_count,
       count(*) total
from statistic_records
group by home_team_id, guest_team_id;

with the following result

and have no idea how to limit records quantity for each team.

Any ideas?

>Solution :

I think you can use Analytical function to achieve that –

SELECT guest_team_id,
       home_team_id,
       count(drawn)          drawn_count,
       count(home_team_lost) home_team_lost_count,
       count(home_team_won)  home_team_won_count,
       count(*) total
from (SELECT *, ROW_NUMBER() OVER(PARTITION BY home_team_id ORDER BY id) rn
        FROM statistic_records)
WHERE rn <= 10
group by home_team_id, guest_team_id;