How do I select non-deleted records from a history table?

Question

I have a history table table where i keep all the records whether they are inserted, updated or deleted. I want to select only the latest record as per below example. Example, Below are two transactions 23 and 24. I want to pick the latest update of 23 and want to skip 24 because it is deleted at the end. Please write a query for me.

Source:

Transaction  Flag   Date        
23            I    1/1/2020     
23            U    2/1/2020         I-Inserted
23            U    3/1/2020         U-Updated
23            U    4/1/2020         D-Deleted
24            I    1/1/2020     
24            U    2/1/2020     
24            D    3/1/2020     


Result:

Transaction  Flag      Date     
23            U    4/1/2020     

Answer 1

One method uses correlated subqueries:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.transaction = t.transaction) and
      t.flag <> 'D';

Answer 2

I don't know whether you are using MySQL or SQL Server, but assuming MySQL 8+ or SQL Server, here is a version which should work:

SELECT "Transaction", Flag, Date
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "Transaction" ORDER BY Date DESC) rn
    FROM yourTable
) t
WHERE rn = 1 AND Flag <> 'D';

Demo

The demo is in SQL Server, but the basic code should run on any database which supports ROW_NUMBER.