Get the last message from each conversation

0
votes

I have table for messages which contains id, sender_id, receiver_id, message and conversation_id

(I connected them by conversation_id, which they make by sending first message and if someone is replying, first he search conversation_id from messages where he is receiver and guy where is he replying is sender and by that send message with same conversation_id)

Now in messages list I want to output one just one last row per different conversation_id where sender_id='$my_id' OR receiver_id='$my_id'

I am using DISTINCT but I get all rows always as output:

SELECT DISTINCT conversation_id, sender_id, message 
FROM messages 
WHERE receiver_id='$my_id' 
ORDER BY id DESC
2
mysqldistinct
If you like, consider following this simple two-step course of action:1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. - Strawberry
you can use LIMIT 1 if you want last 1 record. - shubham715
Just LIMIT 1 bro. - Peter Chaula
How do you define last row for each conversation? Is it identified by the row having max value of id? - 1000111
@1000111 Like if I have 20 rows for conversation between me and you and i have 30 rows for conversation between me and someonee else And if I say mine and tours conversation_id is same in every 20 rows and it is like 333 and for my other conversation it is 444 I want to get last row with highest id thats why I use ORDER BY id DESC and to output only one row per conversation_id. One for our conversation_id='333' and one for ='444' - Skyey

2 Answers

1
votes

try this

SELECT id,conversation_id, sender_id, receiver_id, message FROM message WHERE receiver_id='$my_id' GROUP BY `conversation_id` ORDER BY id DESC LIMIT 1
0
votes

Please give the following query a try:

SELECT 
M.*
FROM messages M
INNER JOIN 
(
    SELECT 
    MAX(id) AS last_id_of_conversation,
    conversation_id
    FROM messages
    GROUP BY conversation_id
) AS t
ON M.id = last_id_of_conversation

Explanation:

SELECT 
 MAX(id) AS last_id_of_conversation,
 conversation_id
FROM messages
GROUP BY conversation_id;

This inner query will generate an output where there will be one row for each conversation_id along with the last id value (or max id) of the conversation.

Later make an inner join between your main table (messages) and the result returned by the inner query on matching id. I hope id is primary key. If so, then the above query ensures to bring a single row (more specifically the row having the last message) for each conversation.

EDIT:

SELECT 
M.*
FROM messages M
INNER JOIN 
(
    SELECT 
    MAX(id) AS last_id_of_conversation,
    conversation_id
    FROM messages
    WHERE sender_id = ? OR receiver_id = ?
    GROUP BY conversation_id
) AS t
ON M.id = last_id_of_conversation