Reputation: 4364
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).
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.
Upvotes: 3
Views: 97
Reputation: 272436
Assuming that higher id
values indicate more recent messages, you can do this:
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
Upvotes: 1
Reputation: 1817
There are two parts of your query in the following order:
So, lets get the latest message for a conversation between UserID a and UserID b:
SELECT *
FROM message
WHERE (toUserID, userID) IN ((a, b), (b, a))
ORDER BY message.time DESC
LIMIT 1
Then you want these to be combined for the two conversations between UserIDs 1 and 2 and UserIDs 1 and 4. This is where the union comes into play (we do not need to check for duplicates, thus we use UNION ALL, thanks to Marcus Adams, who brought that up first).
So a complete and straightforward solution would be:
(SELECT *
FROM message
WHERE (toUserID, userID) IN ((2, 1), (1, 2))
ORDER BY message.time DESC
LIMIT 1)
UNION ALL
(SELECT *
FROM message
WHERE (toUserID, userID) IN ((4, 1), (1, 4))
ORDER BY message.time DESC
LIMIT 1)
And as expected, you get message 3 and 6 in your SQLFiddle.
Upvotes: 1
Reputation: 69819
The below works as required:
SELECT m1.*
FROM Message m1
LEFT JOIN Message m2
ON LEAST(m1.toUserID, m1.userID) = LEAST(m2.toUserID, m2.userID)
AND GREATEST(m1.toUserID, m1.userID) = GREATEST(m2.toUserID, m2.userID)
AND m2.time > m1.Time
WHERE m2.MessageID IS NULL
AND ( (m1.toUserID IN (2,4) AND m1.userID = 1)
OR (m1.userID IN (2,4) AND m1.toUserID = 1)
);
To simplify how this works, imagine you just wanted the latest message sent by userid 1, rather than having to match the to/from tuples as this adds clutter to the query that doesn't help. To get this I would use:
SELECT m1.*
FROM Message AS m1
LEFT JOIN Message AS m2
ON m2.UserID = m1.UserID
AND m2.time > m1.time
WHERE m1.UserID = 1
AND m2.MessageID IS NULL;
So, we are joining similar messages, stipulating that the second message (m2) has a greater time than the first, where m2 is null it means there is not a similar message with a later time, therefore m2 is the latest message.
Exactly the principal has been applied in the solution, but we have a more complicated join to link conversations.
I have used LEAST
and GREATEST
in the join, the theory being that since you have 2 members in your tuple (UserID, ToUserID)
, then in any combination the greatest and the least will be the same, e.g.:
From/To | Greatest | Least |
--------+-----------+-------+
1, 2 | 2 | 1 |
2, 1 | 2 | 1 |
1, 4 | 4 | 1 |
4, 1 | 4 | 1 |
4, 2 | 4 | 2 |
2, 4 | 4 | 2 |
As you can see, in similar From/To the greatest and the least will be the same, so you can use this to join the table to itself.
Upvotes: 1
Reputation: 53880
You can do this easily by separating it into two queries with ORDER BY
and LIMIT
then joining them with UNION
:
(SELECT *
FROM message
WHERE (toUserID IN (2,4) AND userID = 1)
ORDER BY message.time DESC
LIMIT 1)
UNION
(SELECT *
FROM message
WHERE (userID IN (2,4) AND toUserID = 1)
ORDER BY message.time DESC
LIMIT 1)
The parenthesis are important here, and this returns messages 2 and 6, which seems correct, not 3 and 6.
It also seems like you could use UNION ALL
for performance instead of UNION
because there won't be duplicates between the two queries, but it's better if you decide that.
Here's your data:
MESSAGEID USERID TOUSERID MESSAGE TIME
1 1 2 nachricht 1 123
2 1 2 nachricht 2 124
3 2 1 nachricht 3 125
4 3 2 nachricht wrong 1263
5 2 4 nachricht wrong 1261
6 4 1 nachricht sandra 126
Upvotes: 1