SQL to get the most recent record preferences

Question

I am in the process of building a mock up preference centre and for this of course i need the assumption that a Subscriber Key can exist multiple times in the data extension where i am saving the preferences updates.

I want to be able to correctly get via SQL the latest entry of a Subscriber Key. In the query below I need to be able to retrieve however has a preference for Winter =True and for Europe= True.

As you can see from the print screen attached i have 2 Subscriber Keys but the most recent entry does not have these preferences as true. I would expect my query to return 0 (as that's the most recent info i have for that subscriber) however the result returns 1 (associated with the first time i submitted the preference mock up cloud page).

select b.[Contact ID],b.[Booking: Booking Number],b.[First Name],b.[Booking Passenger Email],a.DateSubmitted
from 
(select SubscriberKey,max(Datesubmitted) as [DateSubmitted],Europe,Winter from [AUS mock Preference Centre - NO primary key]
group by SubscriberKey,Europe,Winter) as a
JOIN [MTP TT JB CRIS 2] as b 
ON b.[Contact ID]=a.SubscriberKey
where (a.Europe='True' and a.Winter='True')

How should i amend my query in order to be able to retrieve my data successfully?

Giulietta

Answer 1

I believe it would be something like this:

select
b.[Contact ID],b.[Booking: Booking Number],b.[First Name],b.[Booking Passenger Email],a.DateSubmitted
from (
    select 
    aus.SubscriberKey as sKey,aus.DateSubmitted,aus.Europe,aus.Winter
    , row_number() over (partition by aus.SubscriberKey order by aus.DateSubmitted desc) as row
    from [AUS mock Preference Centre - NO primary key] as aus
) as a
JOIN [MTP TT JB CRIS 2] as b 
ON b.[Contact ID]=a.sKey
where (a.Europe='True' and a.Winter='True' and a.row = 1)

You don't need group by because you want to receive a single row and you do not want to aggregate it.

I havent tested this but its usually the way to go, i guess ;) You can also watch the row number and partition function here