Power By 专否社区

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

Go To StackoverFlow.com

2

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!

2012-04-03 21:03
by Hoppe
Can you post some sample data and desired o/ - Teja 2012-04-03 21:04

5

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!

2012-04-03 21:36
by Nikola Markovinović
this is awesome, but how come I can't do: 'select top 1 with ties MessageID, MessageSent, MessageSenderUserID, MessageRecipientUserID from [Messaging_Message] ORDER BY MessageSent...' @Nikola Markovinovi - Hoppe 2012-04-04 13:10
Because MessageSent has different values for different MessageSenderUserIDs. rownumber() assigns 1 to each topmost MessageSent, translating ates into their position by descending order, then top 1 with ties selects only rows having rownumber = 1. This is a special case, you cannot even say select top 2... It works for topmost value per group only - Nikola Markovinović 2012-04-04 13:23

1

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
2012-04-03 21:23
by Umut Derbentoğlu
if I put a where clause on the select within the with statement to differentiate per user, and then have concurrent users accessing, will I have issues? I'm new to temp result set - Hoppe 2012-04-04 13:21

0

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
2012-04-03 21:18
by Tim Lehner
Ads