How to select multiple maximum rows from a SQL Server table

advertisements

I need to select rows with max(batchid) for each day.

Sample table:

Id | BatchId |Date                 |KeyValue
-- | --------|---------------------|-----------
1  | 1       | 2016-12-13 12:30:66 |1234
2  | 1       | 2016-12-13 12:30:66 |5654
3  | 2       | 2016-12-13 08:30:66 |1234
4  | 2       | 2016-12-13 08:30:66 |5654
5  | 1       | 2016-12-12 12:10:45 |1234
6  | 1       | 2016-12-12 12:10:45 |5634
7  | 2       | 2016-12-12 08:10:45 |1234
8  | 2       | 2016-12-12 08:10:45 |5634
9  | 3       | 2016-12-12 04:10:45 |9628

Expected output:

Id | BatchId |Date                 |KeyValue
-- | --------|---------------------|-----------
3  | 2       | 2016-12-13 08:30:66 |1234
4  | 2       | 2016-12-13 08:30:66 |5654
9  | 3       | 2016-12-12 04:10:45 |9628

Thanks in advance !


Use max() over . . .:

select t.*
from (select t.*, max(date) over (partition by cast(date as date)) as maxdate
      from sample t
     ) t
where maxdate = date;

Or, rank()/dense_rank():

select t.*
from (select t.*,
             dense_rank() over (partition by cast(date as date) order by date desc) as seqnum
      from sample t
     ) t
where seqnum = 1;