How to limit a query in Marketing cloud

Question

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

Answer 1

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).

Answer 2

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)