0

So, I have a data extension where data is being saved.

My data extension (CustomerSourceDE) looks something like this:


Id (text) NULLABLE

Email (Email) NULLABLE

Customer_FirstName (Text) NULLABLE

Status (Text) NULLABLE

RecordTimestamp (Date) default value - CURRENT DATETIME


Records in this data extension looks like this (one customer MAY have multiple rows which is why no primary key is defined)


1 | test1@test.com | Michael | New | Sunday, July 15, 2018 10:08 PM

2 | test2@test.com | Jackson | New | Sunday, July 15, 2018 10:10 PM

3 | test3@test.com | JordanAB | Accepted | Sunday, July 15, 2018 10:15 PM

1 | test1@test.com | Michael | Rejected | Sunday, July 15, 2018 10:25 PM

7 | test7@test.com | RobertMM | Rejected | Sunday, July 15, 2018 10:31 PM

2 | test2@test.com | Jackson | Disabled | Sunday, July 15, 2018 10:38 PM


In my Target Data Extension, I want DISTINCT records only and if there are multiple rows of same Id then I want most recent record row only based on the field (RecordTimestamp).

I have created this query but now getting the desired result (for some reason, I am getting 1 single record).

My desired result is this that I want in a new target data extension (MyTargetDE):


3 | test3@test.com | JordanAB | Accepted | Sunday, July 15, 2018 10:15 PM

1 | test1@test.com | Michael | Rejected | Sunday, July 15, 2018 10:25 PM

7 | test7@test.com | RobertMM | Rejected | Sunday, July 15, 2018 10:31 PM

2 | test2@test.com | Jackson | Disabled | Sunday, July 15, 2018 10:38 PM


My sql query which is NOT working and I need help with:

SELECT * FROM CustomerSourceDE
WHERE
Id IN (SELECT DISTINCT Id from CustomerSourceDE where AddRecordTimestamp =  (SELECT MAX(RecordTimestamp) FROM CustomerSourceDE as t WHERE Id = t.Id))
2

One way to approach this is by writing a subquery to assign an ordered row number by customer ID, which you can then use to filter on.

SELECT ID
    , Email
    , Customer_FirstName
    , Status
    , RecordTimeStamp
FROM (      
SELECT ID
    , Email
    , Customer_FirstName
    , Status
    , RecordTimeStamp
    , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RecordTimeStamp DESC) as ID_Row
FROM CustomerSourceDE
) as A
WHERE ID_Row = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.