Thanks Gail, but I'm not sure how to achieve that.
I've been doing this (which I thought might work), but I still get multiple rows per ContactID

SELECT ContactID, RenewalDate
FROM SubscriptionMembers
WHERE RenewalDate IN
(SELECT MAX(RenewalDate)
FROM SubscriptionMembers
GROUP BY ContactID)
ORDER BY ContactID