Select most recent (top 1) from table grouped by another ID

sql, sql-server-2008

Solution

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!

Problem

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 ``` Thanks!

Original source