By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Transactions
( SeqNo INT
, ID VARCHAR(20)
, Status VARCHAR(20)
, PurchaseDate DATE
, Cost INT
, Units INT
);
/*
SeqNo ID Status PurchaseDate Cost Units
99 ABC123 New 2019-01-01 100 20
98 ABC123 Mid 2019-01-01 50 30
97 ABC123 Cat 2020-01-01 25 40
96 ABC123 Old 2020-01-01 0 50
99 DEF456 Some 2019-03-04 900 60
98 DEF456 Thing 2019-03-04 1000 70
97 DEF456 Else 2020-03-04 15000 8
*/
INSERT INTO Transactions
(SeqNo, ID, Status, PurchaseDate, Cost, Units)
VALUES
(99, 'ABC123', 'New', '2019-01-01', 100 , 20)
, (98, 'ABC123', 'Mid', '2019-01-01', 50 , 30)
, (97, 'ABC123', 'Cat', '2020-01-01', 25 , 40)
, (96, 'ABC123', 'Old', '2020-01-01', 0 , 50)
, (99, 'DEF456', 'Some', '2019-03-04', 900 , 60)
, (98, 'DEF456', 'Thing','2019-03-04', 1000, 70)
, (97, 'DEF456', 'Else', '2020-03-04', 15000, 8 );
Records: 7 Duplicates: 0 Warnings: 0
WITH get_data AS (
SELECT SeqNo
, ID
, PurchaseDate
, LAST_VALUE(status)
OVER (PARTITION BY ID, PurchaseDate
ORDER BY SeqNo
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS status
-- or last_status to not confuse it
, Cost
, Units
, ROW_NUMBER()
OVER (PARTITION BY ID, PurchaseDate
ORDER BY SeqNo) AS row_num
FROM Transactions
)
SELECT * FROM get_data WHERE row_num = 1
| SeqNo | ID | PurchaseDate | status | Cost | Units | row_num |
|---|---|---|---|---|---|---|
| 98 | ABC123 | 2019-01-01 | New | 50 | 30 | 1 |
| 96 | ABC123 | 2020-01-01 | Cat | 0 | 50 | 1 |
| 98 | DEF456 | 2019-03-04 | Some | 1000 | 70 | 1 |
| 97 | DEF456 | 2020-03-04 | Else | 15000 | 8 | 1 |