How to get the best 2 highest amounts per day

Question

My table data is as follows:

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 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 based on the sum of the sums, and the highest two total values ​​should be returned per day if the amounts are the same based on the record identifier, and the date of the 2 records should be returned.

My desired result:

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

Answer 2

Please read my comment on the question. Here is an example request:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY dt ORDER BY amt DESC) AS RowNo
    FROM @t
) T 
WHERE RowNo IN (1, 2)
ORDER BY dt DESC

[EDIT]

A more adequate sample to describe the problem:

SELECT t.*
FROM (
    SELECT name, dt, SUM(amt) AS amt, ROW_NUMBER() OVER(PARTITION BY dt ORDER BY SUM(amt) DESC) AS RowNo
    FROM @t
    WHERE dt BETWEEN ... AND ...
    GROUP BY name, dt 
) T
WHERE RowNo IN (1, 2) 
ORDER BY dt DESC

Answer 3

. row_number() CTE. - / varchar . datetime, . ( ), date.

:

select t.*
from (select name, CAST(dt as DATE) as dt, sum(amt),
             row_number() over (partition by name order by sum(amt) desc) as seqnum
      from @t t
      where dt >= CAST(Getdate() - 4 as date) and
            dt <= CAST(Getdate() as date)      
      group by name, CAST(dt as DATE)
     ) t
where seqnum <= 2
order by name, dt desc;

Answer 4

try it

DECLARE @t TABLE (
  name varchar(10),
  amt int,
  dt datetime
)
DECLARE @topRows int = 2

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')

SELECT
  name,
  SUM(amt) [totalsum],
  dt,
  ROW_NUMBER() OVER (PARTITION BY dt ORDER BY SUM(amt) DESC, name) rw INTO #temp
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 dt DESC

SELECT
  *
FROM #temp
WHERE rw <= @topRows
ORDER BY dt DESC

DROP TABLE #temp