SQL Grouping: Most recent record between users.

Question

Maybe I'm having a really bad day, but could someone possibly help me to turn this:

MessageID | SendingUserID | ReceivingUserID
-------------------------------------------
1073      | 1002          | 1001
1065      | 1001          | 1002
1076      | 1008          | 1002

Into:

MessageID | SendingUserID | ReceivingUserID
-------------------------------------------
1073      | 1002          | 1001
1076      | 1008          | 1002

Whereby,only the most recent message between two users is listed?

Answer 1

try this:

SELECT Message.*
FROM Message
WHERE Message.MessageID IN
(SELECT MAX(MessageID) FROM Message 
    GROUP BY 
        CASE WHEN ReceivingUserID > SendingUserID 
            THEN ReceivingUserID ELSE SendingUserID END,
        CASE WHEN ReceivingUserID > SendingUserID
            THEN SendingUserID ELSE ReceivingUserID END
)

Answer 2

The exclusive self join approach:

select *
from YourTable a
left join YourTable b 
    on (
        (a.SendingUserID = b.SendingUserID
        and a.ReceivinggUserID = b.ReceivingUserID)
        or (a.SendingUserID = b.ReceivingUserID
        and a.ReceivinggUserID = b.SendingUserID)
    ) and b.messageid > a.messageid
 where b.messageid is null

The join on "b" searches for later messages between the same users. The WHERE clause filters for messages that do not have a later message. This gives you only the latest message between each pair of users.

Answer 3

You could use a CTE (Common Table Expression) in SQL Server 2005 and higher to make sure the two UserID's are always the smaller one before the larger one, and then just get the maximum for each of those combinations:

WITH Messages(MessageID, User1, User2)
AS 
(
    SELECT
     MessageID,
     CASE 
       WHEN SendingUserID < ReceivingUserID
       THEN SendingUserID
       ELSE ReceivingUserID 
     END as 'User1',
     CASE 
       WHEN SendingUserID < ReceivingUserID
       THEN ReceivingUserID 
       ELSE SendingUserID
     END as 'User2'
    FROM 
     MyMessages
)
SELECT 
    MessageID, User1, User2 
FROM 
    Messages m1
WHERE
    MessageID = (SELECT MAX(MessageID) FROM  Messages m2 
                 WHERE m1.User1 = m2.User1 AND m1.User2 = m2.User2)

The inner SELECT inside the CTE would "order" your messages like this:

MessageID   User1 User2
  1065       1001    1002
  1073       1001    1002
  1076       1002    1008

and the outer SELECT based on this CTE then just simply picks the entry with the maximum MessageID for every combination of (User1, User2).

Marc