I have a table name message and I am trying to get the last message from each conversation which is store in the same table. I tried looking similar question over StackOverflow but seem like no luck. This question is not duplicate since I have to get the last message from by_uid=>to_uid OR to_uid <= by_uid
This is my table message
id by_uid to_uid msg
1 3 4 hi
2 3 4 hello
3 4 3 where
4 3 5 hey there
5 3 6 that's ok
6 6 3 really?
7 4 2 hiiiiii
What I am expecting is
id by_ui to_uid msg
3 4 3 where
4 3 5 hey there
6 6 3 really?
7 4 2 hiiiiii
I am trying to achieve the above output with the below query.
SELECT message.*
FROM message join (select max(id) maxid, to_uid from message group by to_uid) latest
on message.id=latest.maxid and message.to_uid=latest.to_uid
WHERE message.to_uid = 3 OR message.by_uid = 3
