CODEWITHSUNDEEP
sqlsql-serversubquerytop-n
BlueBird
BlueBird

Reputation: 1466

Subquery with Sum of top X rows

I am trying to get the top 5 results for each person from a table. I am able to get the top result for them however I want the sum of the top 5. 

Select Distinct
        r.LastName, 
        r.FirstName , 
        r.Class,
        r.BibNum,
        (Select top 1 r2.points from Results r2 where r2.season=r.Season and r2.Mountain=r.Mountain and r2.Bibnum=r.bibnum Order By r2.Points Desc) as Points
from Results as r Where Season='2015' and Mountain='Ski Resort'
Order By Class, Points Desc

Columns:

    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RaceNum] [int] NOT NULL,
    [BibNum] [int] NOT NULL,
    [FirstName] [nvarchar](max) NULL,
    [LastName] [nvarchar](max) NULL,
    [Sex] [nvarchar](max) NULL,
    [Class] [nvarchar](max) NULL,
    [Team] [nvarchar](max) NULL,
    [FirstRun] [nvarchar](max) NULL,
    [SecondRun] [nvarchar](max) NULL,
    [Best] [nvarchar](max) NULL,
    [Points] [int] NOT NULL,
    [Season] [int] NOT NULL,
    [Mountain] [nvarchar](max) NULL

Upvotes: 1

Views: 179

Answers (1)

Mureinik
Mureinik

Reputation: 311198

You could use row_number() to get the top five rows, and then group by the other fields:

SELECT   LastName, 
         FirstName, 
         Class,
         BibNum,
         SUM(points) 
FROM     (SELECT  LastName, 
                  FirstName, 
                  Class,
                  BibNum,
                  points,
                  ROW_NUMBER() OVER (PARTITION BY LastName, 
                                                  FirstName, 
                                                  Class,
                                                  BibNum
                                     ORDER BY points DESC) AS rn
           FROM   results
           WHERE  Season='2015' and Mountain='Ski Resort'
         ) t
WHERE    rn <= 5
GROUP BY LastName, FirstName, Class, BibNum

Upvotes: 2

Related Questions