how to get top 2 highest sum amounts per day

Question

My table data looks like this

declare @t table (name varchar(10), amt int, dt datetime)

insert into @t (name, amt, dt)
values 
     ('meeseva',100,'06-17-2015'), ('meeseva',200,'06-17-2015'),
     ('meeseva',200,'06-17-2015'), ('meeseva',100,'06-16-2015'),
     ('meeseva',100,'06-15-2015'), ('meeseva',100,'06-14-2015'),
     ('fish',100,'06-17-2015'), ('fish',200,'06-17-2015'),
     ('fish',100,'06-16-2015'), ('fish',200,'06-16-2015'),
     ('fish',100,'06-15-2015'), ('fish',100,'06-14-2015'),
     ('raju',100,'06-17-2015'), ('raju',200,'06-17-2015'),
     ('raju',100,'06-16-2015'), ('raju',100,'06-15-2015'),   
     ('raju',100,'06-14-2015'), ('raju',500,'06-14-2015')

So far I have tried

select 
    name,
    SUM(amt),
    dt,
    ROW_NUMBER() OVER (PARTITION BY name order by dt) 
from
    @t
where 
    dt >= (SELECT CONVERT (VARCHAR(10), Getdate() - 4, 101)) 
    and dt <= (SELECT CONVERT (VARCHAR(10), Getdate(), 101))      
GROUP BY 
    name, dt
ORDER BY 
    name, dt desc

I have data where I need to get data basing on sum of amounts and per day highest two totals should be returned if amounts are same based on record id and date top 2 records should be returned.

My desired output :

name    sum dt  
-----------------------------------
fish    300 2015-06-17 00:00:00.000 
meeseva 500 2015-06-17 00:00:00.000 
fish    300 2015-06-16 00:00:00.000 
raju    200 2015-06-16 00:00:00.000 
fish    100 2015-06-15 00:00:00.000
meeseva 100 2015-06-15 00:00:00.000
raju    600 2015-06-14 00:00:00.000 
meeseva 100 2015-06-14 00:00:00.000 

Answer 1

you can try this

;WITH ranking AS(
SELECT  name,
        SUM(amt) [sum],
        dt,
        RANK() OVER (PARTITION BY dt ORDER BY SUM(amt) DESC, name -- use recordid here to order by sum then recordnumber
        ) AS rnk
FROM    @t
WHERE   dt >= DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
GROUP BY name,
        dt
)
SELECT  name,
        [sum],
        dt
FROM    ranking
WHERE   rnk <= 2
ORDER BY dt DESC,
        name