Grouping sql result by a specific criteria

i am writing a query that supposed to show for a set of users, what emails he/she received, and if the email was open or not.

This is the result of the query:

Product     |   Year    |   Month   |   SendTime    |   UserId      |   EmailTitle      |   Open
That one    |   2018    |   11      |   2018-11-23  |   1000756     |   JoinUs          |   1   
That one    |   2018    |   11      |   2018-11-25  |   1000756     |   JoinUs          |   0   
That one    |   2019    |   1       |   2019-01-04  |   1000756     |   Need Help?      |   0   
That one    |   2019    |   4       |   2019-04-07  |   1000756     |   Win Back        |   0   
That one    |   2019    |   4       |   2019-04-10  |   1000756     |   A gift to you   |   0   

THe problem i have, is that a user can receive the same email more than once per month, and open just one of them. One example is the JoinUs EmailTitle, that was received in the days 23 and 25 but just the 23 mail was open.

So in this case, when i have two equal emails send, i need to show just one line, and i need to show just the one that was open, preferably.

SELECT  S.Product, 
        YEAR(S.SendTime) [Year],
        MONTH(S.SendTime) [Month],
        S.SendTime SendTime, 
        S.UserId,
        M.EmailName,
        CASE WHEN Mo.ContactID IS NOT NULL THEN 1 ELSE 0 END [Open]
FROM MailSend S
JOIN CommMapping2 M on M.EmailName = S.EmailName
LEFT JOIN MailOpen Mo ON Mo.JobId = S.JobId AND Mo.BatchID = S.BatchID AND Mo.ContactID = S.ContactID 
WHERE S.UserId IS NOT NULL AND S.UserId = '1000756'

Answer

You can use rownumber and make a partition by userid and EmailTitle. That way in case of two emails you will get only the open one.

with cte as (
SELECT  S.Product, 
        YEAR(S.SendTime) [Year],
        MONTH(S.SendTime) [Month],
        S.SendTime SendTime, 
        S.UserId,
        M.EmailName,
        CASE WHEN Mo.ContactID IS NOT NULL THEN 1 ELSE 0 END [Open],
        ROW_NUMBER() over (partition by UserId,  EmailTitle order by  CASE WHEN Mo.ContactID IS NOT NULL THEN 1 ELSE 0 END desc) as number 
FROM MailSend S
JOIN CommMapping2 M on M.EmailName = S.EmailName
LEFT JOIN MailOpen Mo ON Mo.JobId = S.JobId AND Mo.BatchID = S.BatchID AND Mo.ContactID = S.ContactID 
WHERE S.UserId IS NOT NULL AND S.UserId = '1000756'
)
select *
from cte 
where number = 1