just go with practical scenario it may happen that email id same but domain different

in that case Just remove substring from "bitbucket-25253" solution

declare @T TABLE

(userid INT, emailname VARCHAR(50),name varchar(10))

INSERT INTO @T

SELECT 1, 'test123test@b.com','Bill' UNION ALL

SELECT 1, 'test123test@a.com','Billiiiii' 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'

;WITH cte

as (select row_number() over(partition by emailname order by userid DESC) as rn,emailname ,name

from @T)

select * from cte where rn = 1