CODEWITHSUNDEEP
sql
Matěj Štágl
Matěj Štágl

Reputation: 1037

Sql group by max column value after CTE

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:

img1

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.

Upvotes: 1

Views: 405

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 2

Related Questions