I have the data in Exhibit 1 of the attachment which I get by running the query shown below. However, what I am looking to do is to get my data to look like Exhibit 2 of the attached so that I only retain the records with the later Mod_Date for every Loan_ID by setting row1=1. Can anyone please offer their input? Any help would be extremely appreciated. Many thanks in advance.
select t.Loan_ID, t.Mod_Option, t.Mod_Date, t.Loan_UPB, t.row1,
row_number() over(partition by Mod_Option) as row2
from(select Loan_ID, Mod_Option, Mod_Date, Loan_UPB,
row_number() over(partition by Loan_ID order by a.Mod_Date) as row1
from Exhibit1) t