CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 4364

Select most recent record based on two conditions

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.

Upvotes: 3

Views: 97

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272446

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

Upvotes: 1

jan.vogt
jan.vogt

Reputation: 1817

There are two parts of your query in the following order:

  1. You want the latest outgoing or incoming message for a conversation between two users
  2. You want these latest messages for two different pairs of users, i.e. conversations.

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

GarethD
GarethD

Reputation: 69829

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

Marcus Adams
Marcus Adams

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

Related Questions