Select most recent record based on two conditions

Question

I have user1 who exchanged messages with user2 and user4 (these parameters are known). I now want to select the latest sent or received message for each conversation (i.e. LIMIT 1 for each conversation).

SQLFiddle

Currently my query returns all messages for all conversations:

SELECT * 
FROM message 
WHERE (toUserID IN (2,4) AND userID = 1)
OR    (userID IN (2,4) AND toUserID = 1)
ORDER BY message.time DESC

The returned rows should be messageID 3 and 6.

Answer 1

Assuming that higher id values indicate more recent messages, you can do this:

• Find all messages that involve user 1
• Group the results by the other user id
• Get the maximum message id per group

SELECT *
FROM message
WHERE messageID IN (
    SELECT MAX(messageID)
    FROM message
    WHERE userID = 1 -- optionally filter by the other user
    OR toUserID = 1 -- optionally filter by the other user
    GROUP BY CASE WHEN userID = 1 THEN toUserID ELSE userID END
)
ORDER BY messageID DESC

Updated SQLFiddle