173 ) 14 ) Query to find duplicate rows of loan id but time is different

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

14 )  Query to find duplicate rows of loan id but time is different 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Due to a bug in an upstream system, a source file sent duplicate records for the same loan account number on the same day. 

Each duplicate row has a different SnapshotTimestamp. 

How would you write a query to select only the most recent row from all duplicate  loans account and filter out the older duplicates?

-----

WITH RankedLoans AS (
    SELECT 
        LoanNumber,
        Timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY LoanNumber 
            ORDER BY creationdate DESC
        ) AS rn
    FROM Loans
)
SELECT 
    LoanNumber,
    creationdate  
FROM RankedLoans 
WHERE rn = 1;
------------------------------------------------------------------------------

SELECT 
    LoanNumber,
    Timestamp 

FROM RankedLoans
WHERE rnk = 1;


---------------