I have table where i have Status (Delete and RQT) with Entry_Date (DateTime) and Vin number.
3KPA351A1JE072740 - 2018-01-23 08:12:27.560 - Delete
3KPA351A1JE072740 - 2018-01-22 15:32:01.000 - RQT
In the above table i need ony one record with Vin,Entry_Date, status either (Delete or RQT) with maximum Entry_Date Value i get the above result with this query.
Select Vin,Max(Entry_Date) as Entry_Date1,Status
from VS_Transaction where Status='RQT' or Status='Delete'
group by Vin,Status order by Vin
hi
i know this topic from long ago
i tried something different
if it helps great


Drop Create Data ...
use tempdb
go
drop table vs_transaction
go
create table vs_transaction
(
Vin varchar(100) null ,
entry_date datetime null ,
Status varchar(100) null
)
go
insert into vs_transaction select '3KPA351A1JE072740','2018-01-23 08:12:27.560','Delete'
insert into vs_transaction select '3KPA351A1JE072740','2018-01-22 15:32:01.000','RQT'
go
select * from vs_transaction
go
SQL
;WITH cte
AS (SELECT vin,
Max(entry_date) AS Entry_Date1
FROM vs_transaction
WHERE status = 'RQT'
OR status = 'Delete'
GROUP BY vin)
SELECT a.*
FROM vs_transaction a
JOIN cte b
ON a.vin = b.vin
AND a.entry_date = b.entry_date1
Results