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