Sql group by max column value after CTE

Question

I have this query:

WITH messages_ranked 
AS ( 
   SELECT p.Date, p.RecipientId, p.RecipientType, p.Id, p.text, p.userId,
   ROW_NUMBER() OVER(PARTITION BY p.RecipientId, p.userId 
   ORDER BY p.Id DESC) 
   AS rk 

   FROM ChatMessages p
   JOIN ChatGroupMemberships as g 
   ON p.recipientId = g.groupId
   WHERE g.userId = XXX <-- user id
) 

SELECT date, recipientId as groupId, recipientType as groupType, id, text, userId, rk
FROM messages_ranked s 
where rk = 1

Order BY s.date DESC 

Which yields me this:

What I'd need is to reduce result rows of this query so that for every unique groupId only the row with highest value of date would be returned.

So for example from the first three rows only the first would be returned, as they share the same groupId and the first row has the newest date.

I tried to follow example here How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? as my question is closely related but I can't seem to do it right.

Answer 1

I am guessing that this does what you want:

WITH messages_ranked AS ( 
      SELECT p.Date, p.RecipientId, p.RecipientType, p.Id, p.text, p.userId,
             ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum
      FROM ChatMessages p JOIN
           ChatGroupMemberships as g 
           ON p.recipientId = g.groupId
      WHERE g.userId = XXX <-- user id
     ) 
SELECT date, recipientId as groupId, recipientType as groupType, id, text, 
       userId, seqnum
FROM messages_ranked s 
WHERE seqnum = 1
ORDER BY s.date DESC ;

I don't think you need to use row_number() twice.