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?
Asked
Active
Viewed 46 times
0
-
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) Just change `1` to `2` in those answers – Charlieface Oct 05 '21 at 10:40
2 Answers
0
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
Ruben Ravnå
- 659
- 7
- 17
-
Almost solved the problem! I tried getting rid of 1 column in partition by clause, keep column ID only (=> partition by ID) and the column rn then returned the right values (at first it just returns 1 for all rows), so that ended up with the correct result. Thanks again, Ruben. – Hoàng Thông Nguyễn Oct 05 '21 at 07:24
0
You can Try This:
select top(2) Id , name , sum(Score) as sumScore from table group by id , name order by sum(Score) desc
vahidrobati
- 71
- 5