Sql Server group by multiple columns uniqueness only on one column

Question

I am building a system to send emails and I am needing to go into the [User] table to retrieve the email to send to, as well as a GUID(uniqueidentifier type) which will be used for unsubscribe purposes.

It is for an ecommerce solution so because anonymous and logged in users can have the same email addresses it creates duplicate entries of emails that need to be distinct in the query.

I am having trouble finding a solution to retrieve the email and the guid but only be distinct on the email. Here is my query so far.

SELECT Email, User_GUID
FROM [User]
WHERE 
    IsActive = 1 AND 
    IsEmailValid = 1 AND
    IsNotActiveBecauseUnsubscribed = 0 AND
    Subscribed = 1
GROUP BY Email, User_GUID

Answer 1

with cte
as
(
    select *, row_number() over (partition by Email order by User_GUID) RowNumber
    from [User]
    where 
        IsActive = 1 and 
        IsEmailValid = 1 and 
        IsNotActiveBecauseUnsubscribed = 0 and 
        Subscribed = 1
)
select Email, User_GUID
from cte
where RowNumber = 1

or

select Email, User_GUID
from
(
    select *, row_number() over (partition by Email order by User_GUID) RowNumber
    from [User]
    where 
        IsActive = 1 and 
        IsEmailValid = 1 and 
        IsNotActiveBecauseUnsubscribed = 0 and 
        Subscribed = 1
) tt
where RowNumber = 1