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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments