Find first n sums of a column, grouped by two other columns-CodePudding

I have a table in SQL like this. Now I want to find sum of score grouped by column ID & Name, and show just two highest sums for each ID as below, so how can I solve this?

CodePudding user response：

Can you try something like this:

Select ID, Name, Score From (
Select ID, Name, SUM(Score) score, row_number() over (partition by ID,Name order by SUM(Score) desc) rn 
from Table
group by ID,Name) allScores
where rn > 2

CodePudding user response：

You can Try This:

select top(2) Id , name , sum(Score) as sumScore from table group by id , name order by sum(Score) desc