SELECT
a.Subscriberkey,
a.MobileNumber,
a.OptOutDate,
a.OptInDate,
a.Keyword
FROM (
SELECT b.MobileNumber,
b.Subscriberkey,
b.OptOutDate,
b.OptInDate,
kl.SharedKeyword as Keyword,
ROW_NUMBER() OVER (PARTITION BY MobileNumber,SubscriptionDefinitionID ORDER BY OptInDate DESC, OptOUtStatusID DESC) AS rn
FROM SMSSubscriptionLog b
INNER JOIN SMS_Keyword_Lookup kl ON kl.KeywordID = b.SubscriptionDefinitionID
) a
WHERE a.rn = 1