Geek Answers Handbook

Sql server group by the uniqueness of several columns on only one column

I am creating a system for sending emails, and I need to go to the [User] table to get the email for sending, as well as the GUID (unique identifier) ​​that will be used for mailing purposes.

This is an e-commerce solution, since anonymous and registered users can have the same email addresses, it creates duplicate email records that should be different in the request.

I'm having trouble finding a solution to retrieve email and index, but I can just distinguish it by email. Here is my request.

SELECT Email, User_GUID
FROM [User]
WHERE 
    IsActive = 1 AND 
    IsEmailValid = 1 AND
    IsNotActiveBecauseUnsubscribed = 0 AND
    Subscribed = 1
GROUP BY Email, User_GUID
+5
2
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


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
+8

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

More articles:


All Articles