CODEWITHSUNDEEP
mysqlsqlselectgroup-bychat
Igor
Igor

Reputation: 673

Select latest message of each conversation

I've two tables:

chat (from is just who have sent the first message)

id | id_from | id_to
1  | 1       | 2
2  | 1       | 3
3  | 3       | 2
4  | 4       | 1

message

id | content | date                | id_chat | id_from | id_to
1  | hi      | 2017-10-04 23:14:41 | 1       | 1       | 2
2  | hello   | 2017-10-04 23:15:03 | 1       | 2       | 1
3  | heey    | 2017-10-04 23:40:00 | 4       | 4       | 1

And I want to get last message of each conversation.

I tried (1 is the user logged id):

SELECT MAX(id) AS idMessage, id_from, id_to, content, date 
FROM message 
WHERE 1 IN (id_from, id_to) 
GROUP BY id_chat
ORDER BY idMessage 
DESC LIMIT 10

I'm getting the latest conversations, but I get the first message of each conversation, not the last one. How can I get the last message?

Upvotes: 3

Views: 1418

Answers (2)

juergen d
juergen d

Reputation: 204766

select m1.*
from messages m1
join
(
  SELECT MAX(id) as id
  FROM message
  GROUP BY id_chat
) m2 on m1.id = m2.id

Upvotes: 5

nmanikumar5
nmanikumar5

Reputation: 65

SELECT m.content as idMessage, m.id_from, m.id_to, m.date  from message as m JOIN chat c WHERE <Logged_ID> = m.id_chat ORDER BY m.date DESC LIMIT 1

Upvotes: 1

Related Questions