This has already been answered but it's interesting that the logical requirement exactly matches an existing function which isn't used in any of the solutions offered. What you're looking for - the logical requirement - is the maximum userid per partition of email, and the function is;

MAX(userid) OVER(PARTITION BY email)

Here's the query;

;WITH SampleData (userid, email, name) AS (

SELECT 1, 'a@a.com', 'Bill' UNION ALL

SELECT 2, 'a@a.com', 'Billy' UNION ALL

SELECT 3, 'a@a.com', 'William' UNION ALL

SELECT 4, 'b@b.com', 'John' UNION ALL

SELECT 5, 'b@b.com', 'Johnny' UNION ALL

SELECT 6, 'c@c.com', 'Jimmy' UNION ALL

SELECT 7, 'c@c.com', 'Jim'

),

MyQuery AS (

SELECT userid, email, name,

ChosenUserID = MAX(userid) OVER(PARTITION BY email)

FROM SampleData

)

SELECT userid, email, name

--, ChosenUserID

FROM MyQuery

WHERE userid = ChosenUserID

ORDER BY userid

It's unlikely that it will perform any different to the other solutions, but you can deduce - at a swift glance - the intent of the code.