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?