mohan111 mohan111 - 5 months ago 21
SQL Question

How to get latest records based on Batch ID

declare @tab table
(
BatchID INT,
Code VARCHAR(20),
CommType INT,
LastStatus VARCHAR(5),
SourceModiifedLastDate varchar(30)
)

INSERT INTO @tab(BatchID, Code, CommType, LastStatus, SourceModiifedLastDate)
VALUES (1, 'A003-3', 3, 'I', '2013-06-17 21:28:01.827'),
(2, 'A004-1', 1, 'I', '2014-06-17 21:28:01.827'),
(6, 'A003-3', 3, 'U', '2015-06-17 21:28:01.827'),
(9, 'A003-3', 3, 'D', '2015-06-17 21:28:01.827'),
(11, 'A004-1', 3, 'D', '2013-06-17 21:28:01.827'),
(12, 'A004-1', 1, 'I', '2015-06-17 21:28:01.827'),
(16, 'A005-3', 3, 'I', '2011-06-17 21:28:01.827'),
(19, 'A005-3', 3, 'D', '2013-0617 21:28:01.827'),
(20, 'A006-3', 3, 'U', '2011-06-17 21:28:01.827'),
(21, 'A006-3', 3, 'I', '2013-0617 21:28:01.827')

Select * from @tab


Here in my sample data I need to get only Laststatus = 'D' records based on latest BatchID.

For example if you see Code = 'A003-3' it got inserted, updated and deleted I need to get this record

If you see code = 'A004-1' it got inserted, deleted and inserted I don't need this record.

Output should be :

BatchID Code CommType LastStatus SourceModiifedLastDate
---------------------------------------------------------------
9 A003-3 3 D 2015-06-17 21:28:01.827
19 A005-3 3 D 2013-06-17 21:28:01.827


I need to get only latest deleted records based on latest
BatchID
and latest date.

I have tried using
MAX
condition and
GROUP BY
to filter records but I'm unable to get what I'm looking for.

Please help me find a solution

Answer
select tt.*
from (select t.*
           , row_number() over (partition by Code order by BatchId desc) as rn
      from @tab t
     ) tt
where tt.rn = 1 
  and tt.LastStatus = 'D';