SQLTeam.com | Weblogs | Forums

Maximum Date with Status


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


Something like:

select top(1) with ties
  from vs_transaction
 where status in ('RQT','Delete')
 order by rownumber() over(partition by vin
                               order by entry_date desc