0

I am trying to query a data extension which stores email to get back subscriber key, the DE stores email from a cloud page form so that I could redirect them to a profile center by making a custom URL based on the subscriber key, the issue is the query returns subscriber key for all email in data extension and I am trying to limit it to the 1 entered. I know the limit doesnt work with a join but is there any way to limit it to just one record or is there any better way of getting subscriber key for the email entered.

SELECT s.email ,c.SubscriberKey
FROM InputDE s
LEFT JOIN _Subscribers c 
ON c.EmailAddress = s.email
0
2

If you mean "de-duplicate by email address" when you say "limit it to just one record", then you can do something like this:

SELECT top 1 with ties
  s.email 
, c.SubscriberKey
FROM InputDE s
INNER JOIN ent._Subscribers c ON c.EmailAddress = s.email
order by row_number() over (paritition by s.email order by s._customObjectKey desc)

This query picks the most recent (top 1 with ties) email (paritition by s.email) in InputDE where the recency is based on the hidden id field _customObjectKey -- a higher value is newer (order by s._customObjectKey desc).

2
  • I am getting this error with your query "The 'Ent.' prefix can only be used to query data in a parent account from a child account." – Sam Jan 7 at 19:13
  • Just remove the ent., since you're running this at the parent. Leaving it off on queries in the child BUs is something I see often. – Adam Spriggs Jan 7 at 20:50
0

This slight change to Adam's solution works for me.

 SELECT top 1  s.email,c.SubscriberKey
FROM InputDE s
INNER JOIN _Subscribers c ON c.EmailAddress = s.email
order by row_number() over (partition by s.email order by s._customObjectKey desc)
1
  • leaving off the with ties is not going to deduplicate it by email. – Adam Spriggs Feb 3 at 18:43

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.