Can I pull DISTINCT customers with SQL based on most recent row record only?

Question

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 | [email protected] | Michael | New | Sunday, July 15, 2018 10:08 PM

2 | [email protected] | Jackson | New | Sunday, July 15, 2018 10:10 PM

3 | [email protected] | JordanAB | Accepted | Sunday, July 15, 2018 10:15 PM

1 | [email protected] | Michael | Rejected | Sunday, July 15, 2018 10:25 PM

7 | [email protected] | RobertMM | Rejected | Sunday, July 15, 2018 10:31 PM

2 | [email protected] | 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 | [email protected] | JordanAB | Accepted | Sunday, July 15, 2018 10:15 PM

1 | [email protected] | Michael | Rejected | Sunday, July 15, 2018 10:25 PM

7 | [email protected] | RobertMM | Rejected | Sunday, July 15, 2018 10:31 PM

2 | [email protected] | 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))

Answer 1

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