conor conor - 4 months ago 13
SQL Question

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!

Answer
;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