Select Last Message Of Conversation For Each Conversation In Mysql

my query: SELECT * FROM messages_status mst INNER JOIN messages msg ON mst.msg_id=msg.id WHERE mst.user_id = 1 AND mst.status NOT IN (0,3) ORDER BY

Solution 1:

Check this out:

SELECT *
  FROM messages_status mst
       INNER JOIN (SELECT messages.* FROM messages INNER JOIN (
                    SELECT sender_ID,conv_id,MAX(created_at) as maxtime FROM messages
                    GROUPBY sender_ID,conv_id) as t1 ON
                    t1.sender_id = messages.sender_id AND t1.conv_id = messages.conv_id AND 
                    t1.maxtime = messages.created_at) msg 
                    ON mst.msg_id=msg.id
WHERE mst.user_id = 1AND mst.status NOTIN (0,3)
ORDERBY msg.created_at DESC

this is the link

Solution 2:

As you asked get the last message of each conversation You may try this

select*from messages msg
innerjoin
(
  selectmax(created_at) created_at
  from messages
  groupby conv_id
) m2
  on msg.created_at = m2.created_at

InnerJOIN messages_status mst ON msg.id=mst.msg_id 
WHERE mst.user_id =1AND mst.status NOTIN (0,3)

http://sqlfiddle.com/#!2/be010/89

Solution 3:

SQLFIDDLEExample

Query:

SELECT msg.*FROM messages_status mst
INNERJOIN messages msg ON mst.msg_id=msg.id
LEFTJOIN messages ms2
 ON ms2.conv_id = msg.conv_id
 AND ms2.created_at > msg.created_at
WHERE mst.user_id =1AND mst.status NOTIN (0,3)
 AND ms2.created_at isnull

Result:

|ID|SENDER_ID|CONV_ID|CONTENT|CREATED_AT|UPDATED_AT||----|-----------|---------|---------------|------------------------------|------------------------------||16|3|3|hy:)|March,262014 16:38:17+0000|March,262014 16:38:17+0000||14|1|1|kjhkjhkjhkl|March,262014 16:16:25+0000|March,262014 16:16:25+0000|

Solution 4:

Here is the Fiddle. http://sqlfiddle.com/#!2/be010/116

This is the query which you can use mostly:

SELECT *
FROM 
(SELECT messages.id as id, 
messages.conv_id as conv_id,
max(messages.created_at) as created_at FROM messages,messages_status
WHERE messages.id = messages_status.msg_id
AND messages_status.user_id = 1AND messages_status.status NOTIN (0,3)
GROUPBY messages.conv_id) max_created
INNER JOIN messages 
ON messages.created_at = max_created.created_at
INNER JOIN  messages_status mst 
ON mst.msg_id=max_created.id
GROUPBY messages.conv_id
ORDERBY max_created.created_at DESC;