There is a table:

declare @Results table(ParticipId int, Mark int) insert into @Results values (1, 10) insert into @Results values (1, 14) insert into @Results values (1, 13) insert into @Results values (2, 13) insert into @Results values (2, 13) insert into @Results values (2, 10) select * from @Results

enter image description here

It is necessary in the resulting table to get the sum of the two best values for the Mark column for each ParticipId , ie:

enter image description here

How to make a request for this?

My thoughts revolve around using generic table expressions (CTE) and using a subquery for a query using ORDER BY DESC and TOP 2 . But he didn’t come up with anything practical to get his hands on.

  • And what does "best" mean? - Vadim Ovchinnikov
  • If the best means maximum, then why then for ParticipId=1 SumBest3Mark is 24, not 27? - Regent
  • @Regent, yes - I really have a mistake there. Must be 27 . - Adam

2 answers 2

We enumerate the entries in descending order Mark within the ID and summarize the first and second entries:

 select ParticipId, sum(Mark) from ( select ParticipId, Mark, row_number() over(partition by ParticipId order by Mark desc) RN from @Results ) A where RN<=2 group by ParticipId

    You can use the CROSS APPLY operator to get the sum of the two highest Mark scores for each ParticipId

     SELECT * FROM (SELECT ParticipId FROM @Results GROUP BY ParticipId) g CROSS APPLY (SELECT SUM(Mark) AS SumBest3Mark FROM (SELECT TOP 2 Mark FROM @Results WHERE ParticipId = g.ParticipId ORDER BY Mark DESC) Top2 ) A;