Can i get latest record from data extension having duplicate records

Question

I am updating my target data extension from the data extensions having multiple records by using sql query by union all in automation studio of marketing cloud.

my query is

SELECT 
    FieldA, 
    FieldB,
    FieldC

FROM 
    De1

UNION ALL

SELECT 
    FieldA, 
    FieldB,
    FieldC

FROM 
    De2

This is working fine when both don't have duplicate records.

Now i want that when ever there is a duplicate record in these Data Extension the target Data extension must be updated with the latest records based on time.

Note - FieldC is a Date field that stores the time and date of records.

See image

Here is the records of the data extension. See FieldA having duplicate records with same email twice. Now what i exactly want is when ever my query run it only updated with the latest entry that is abc@gmail.com, Record 2 and Wednesday, July 31, 2019 10:45 AM must be added to target DE

Is That Posible ? or any other way to do this.

if yes then what should i add to update my query.

Answer 1

I don't think it's doable with union using one query. I would create a staging Data Extension that would store records from your union, and then ROW_NUMBER in nested SELECT to get only newest record for each email. You should use ROW_NUMBER function to arrange record according to your needs and select just first record in list with RowNumber = 1

SELECT 
t1.FieldA, 
t1.FieldB, 
t1.FieldC

FROM (
    SELECT 
    FieldA,
    FieldB,
    FieldC,
    ROW_NUMBER() OVER(PARTITION BY FieldA ORDER BY FieldC DESC) AS RowNumber

    FROM StageDE
) t1
WHERE t1.RowNumber = 1

Answer 2

What you need to do is use max() and group by.

SELECT 
FieldA, FieldB, max(FieldC)
FROM DE1
GROUP BY FieldA
UNION ALL
[...]

https://stackoverflow.com/questions/33921629/select-distinct-with-max-using-sql-server

note that: UNION ALL will include duplicates across both your DEs, so if both have the same record in them, you'd have to control for that too.

https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all