Search code examples
sqlsql-servergroup-byrollupssms-2014

Sql Select top 3 per rollup group


How do I return only the top 3 values per Name-datee pair in the following?

DECLARE @t TABLE(NAME NVARCHAR(MAX),datee date,val money)

insert INTO @t SELECT 'a','2012-01-02',100
insert INTO @t SELECT 'a','2012-01-02',100
insert INTO @t SELECT 'a','2012-01-03',100
insert INTO @t SELECT 'a','2012-01-05',150
insert INTO @t SELECT 'a','2012-01-06',200
insert INTO @t SELECT 'b','2012-01-07',200
insert INTO @t SELECT 'b','2012-01-07',400
insert INTO @t SELECT 'b','2012-01-09',500
insert INTO @t SELECT 'b','2012-01-12',600
insert INTO @t SELECT 'b','2012-01-13',100

SELECT Name, datee, SUM(val) sumval from @t 
GROUP BY rollup(NAME ,datee)
order by Name, sumval desc

This current version returns:

Name    datee       sumval
NULL    NULL        2450.00
a       NULL        650.00
a       2012-01-02  200.00
a       2012-01-06  200.00
a       2012-01-05  150.00
a       2012-01-03  100.00
b       NULL        1800.00
b       2012-01-07  600.00
b       2012-01-12  600.00
b       2012-01-09  500.00
b       2012-01-13  100.00

I would like to return:

Name    datee       sumval
NULL    NULL        2450.00
a       NULL        650.00
a       2012-01-02  200.00
a       2012-01-06  200.00
a       2012-01-05  150.00
b       NULL        1800.00
b       2012-01-07  600.00
b       2012-01-12  600.00
b       2012-01-09  500.00

I thought there'd be a simple way but can't figure it out!


Solution

  • ;with cteBase as (
    Select Name
          ,datee
          ,sumval=SUM(val)
          ,rowNr=ROW_NUMBER() over (Partition By Name Order by sum(Val) Desc)
     From @t 
    GROUP BY rollup(NAME ,datee)
    )
    Select * 
     From cteBase 
     Where RowNr<=4
    order by Name, sumval desc
    

    Returns

    Name    datee       sumval  rowNr
    NULL    NULL        2450.00 1
    a       NULL        650.00  1
    a       2012-01-02  200.00  2
    a       2012-01-06  200.00  3
    a       2012-01-05  150.00  4
    b       NULL        1800.00 1
    b       2012-01-07  600.00  2
    b       2012-01-12  600.00  3
    b       2012-01-09  500.00  4