Deleting duplicates based on multiple columns

Question

I have listed duplicates using the following:

select s.MessageId, t.* 
from Message s
join (
    select ToUserId, FromUserId, count(*) as qty
    from Message
    group by ToUserId, FromUserId
    having count(*) > 1
) t on s.ToUserId = t.ToUserId and s.FromUserId = t.FromUserId

Now, how do I delete all but one of the Messages (I'm trying to remove duplicates so I can apply a unique index on FromUserId and ToUserId).

Answer 1

Use a cte and assign row numbers so that all but one for duplicate pairs can be deleted.

with rownums as 
(select m.*, 
 row_number() over(partition by ToUserId, FromUserId order by ToUserId, FromUserId) as rnum
 from Message m)
delete r
from rownums r
where rnum > 1