mysql query two tables, retrieve messages for each conversation

Question

I have two tables.

One of them called "conversations" has the following data.

ConversationID  Sender     Reciever
1               bla1       bla2
2               bla1       bla3
3               bla1       bla4

The other is called "Messages" has the following data.

MessageID    MessageText    TimeAddedMessage        ConversationID
1             helo           2012-03-12 13:00:00          2
2             helo           2012-03-12 13:01:00          1
3             helo           2012-03-12 13:02:00          3
4             helo           2012-03-12 13:03:00          3
5             helo           2012-03-12 13:04:00          2

The result i want from the query is the following:

5             helo           2012-03-12 13:04:00          2
4             helo           2012-03-12 13:03:00          3
2             helo           2012-03-12 13:01:00          1

Which means that we need the most recent comment for each conversation (sorted DESC by time).

Any help appreciated.

user1191247 · Accepted Answer · 2012-03-13T15:38:16.313

3

Try -

SELECT m2.*, c.Sender
FROM (
    SELECT m1.ConversationID, MAX(m1.MessageID) AS MessageID
    FROM Messages m1
    GROUP BY m1.ConversationID
) latest_msg
INNER JOIN Messages m2
    ON latest_msg.MessageID = m2.MessageID
    AND latest_msg.ConversationID = m2.ConversationID
INNER JOIN Conversations c
    ON m2.ConversationID = c.ConversationID
ORDER BY m2.MessageID DESC

EDIT I have modified the above query to include the value of Sender from the Conversations table. I have noticed that your structure for the conversation is a bit odd. A conversation is FROM one user TO another user but there is no way to identify which user wrote each message. Is this intentional?

edited Mar 13 '12 at 15:38

answered Mar 12 '12 at 23:11

user1191247

10,808
2
22
32

I have used MessageID instead of TimeAddedMessage as it is already the PK and should serve the same purpose. – user1191247 Mar 12 '12 at 23:14
Thanks everyone for answering all answers seem to be correct. I started using this one. My problem now is that i need also the name of the user from conversations table (which is bla1). I forgot to mention it in my question. Is this possible to happen in one query? – stefanosn Mar 13 '12 at 15:23
1

I have added the Sender to my query above. I suggest you have a thorough read (reread and reread again) of [MySQL JOIN syntax](http://dev.mysql.com/doc/refman/5.0/en/join.html) – user1191247 Mar 13 '12 at 15:41

score 1 · Answer 2 · answered Mar 12 '12 at 23:10

You can do it by creating derived table with ConversationID and max TimeAddedMessage and joining it back to Messages:

select Messages.MessageID, Messages.MessageText, 
       Messages.TimeAddedMessage, Messages.ConversationID
  from Messages inner join
  (
    select ConversationID, max (TimeAddedMessage) TimeAddedMessage
      from Messages
     group by ConversationID
  ) LastMessages
  on Messages.ConversationID = LastMessages.ConversationID 
     and Messages.TimeAddedMessage = LastMessages.TimeAddedMessage

score 1 · Answer 3 · edited May 23 '17 at 12:11

1

A simple query with good performance for comparitively small datasets.

SELECT m1.*
FROM Messages m1 LEFT JOIN Messages m2
 ON (m1.ConversationId = m2.ConversationId AND m1.TimeAddedMessage < m2.TimeAddedMessage)
WHERE m2.MessageID IS NULL;

Modified from this post.

It's kind of counter intuitive but has become somewhat of an often used cookbook recipe.

edited May 23 '17 at 12:11

Community

1
1

answered Mar 12 '12 at 23:16

cmc

4,294
2
35
34

If you read further down the page you reference, you'll see that this method degrades badly for large datasets. – egrunin Mar 12 '12 at 23:26
The performance of this query depends heavily on the size of the groups. If the number of messages in a conversation is always going to be relatively small then this may well be quicker than the INNER JOIN. – user1191247 Mar 12 '12 at 23:43
Updated response according to your comments – cmc Mar 13 '12 at 02:20

mysql query two tables, retrieve messages for each conversation

3 Answers3