I have a table that contains messages to and from users. How can I use SQL to select all of the messages that the user sent. I want to group the results by MessageSenderUserID. I also want to return only the most recent per recipient.
I tried to use MAX in a having clause, but it seems that's not the correct solution.
I may best describe it as a combination of the following queries:
SELECT TOP 1 MessageID, MessageSent, MessageSenderUserID, MessageRecipientUserID FROM [Messaging_Message] WHERE MessageSenderUserID = 799 ORDER BY MessageSent DESC SELECT MessageSenderUserID FROM [Messaging_Message] GROUP BY MessageSenderUserID
I've only recently learned this (most surprising & fun) way of getting top 1 item in a group:
select top 1 with ties MessageID, MessageSent, MessageSenderUserID, MessageRecipientUserID from [Messaging_Message] order by row_number() over (partition by MessageSenderUserID order by MessageSent desc)
Trick is in order by - results are grouped in partition by part and ordered by sort key, resulting in each group receiving number 1 for first row.
With ties returns all ones. Oh joy of applied mechanic!
WITH TestTableCTE AS ( SELECT RN = ROW_NUMBER() OVER(PARTITION BY MessageSenderUserID ORDER BY MessageSent DESC), MessageID, MessageSent, MessageSenderUserID, MessageRecipientUserID FROM [Messaging_Message] ) SELECT MessageID, MessageSent, MessageSenderUserID, MessageRecipientUserID FROM TestTableCTE WHERE RN=1
This assumes that a user can't send more than one message at the exact same time (it would show more than one message for a user in that case):
SELECT a.MessageID, a.MessageSent, a.MessageSenderUserID, a.MessageRecipientUserID FROM Messaging_Message AS a JOIN ( SELECT MessageSenderUserID, MAX(MessageSent) AS MessageSent FROM Messaging_Message GROUP BY MessageSenderUserID ) AS b ON a.MessageSenderUserID = b.MessageSenderUserID AND a.MessageSent = b.MessageSent