I am having issue on query sorting of last convo messages of each users. Here is my current record on my 2 tables:
I want to achieve below result:
My current query is:
SELECT `messages`.*, `users`.`username`, `u2`.`username` as `to_username`, CASE WHEN to_user_id = 1 THEN `users`.`username` ELSE u2.username END as participant FROM `messages` LEFT JOIN `users` ON `messages`.`from_user_id`=`users`.`id` LEFT JOIN `users` as `u2` ON `messages`.`to_user_id`=`u2`.`id` INNER JOIN (SELECT max(id) as lastmsgId FROM messages where to_user_id = 1 or from_user_id =1 GROUP BY to_user_id) m2 ON `messages`.`id`=`m2`.`lastmsgId` GROUP BY participant ORDER BY `messages`.`created_at` DESC
And the result of my query is:
The 4th and 5th message is not sorted properly I am expecting to display the 4th message instead of 5th message. It displays the older message instead of latest one. I couldn’t find the solution on how will be display the right sorting.
Any help and suggestion is very much appreciated.