This might do it for you:

CREATE TABLE #T(userid INT, emailname VARCHAR(50))

INSERT INTO #T

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'

;WITH cte

as (select row_number() over(partition by substring(emailname,1,7) order by userid DESC) as rn,emailname

from #T)

select * from cte where rn = 1

Results:

rnemailname

1a@a.com William

1b@b.com Johnny

1c@c.com Jim