Amuk Saxena Amuk Saxena - 1 year ago 61
MySQL Question

mysql: Get last conversation records by user

I need to get last conversation records by to_user in Descending order.
I have table called messages. Please have a look on the following screen shot:
enter image description here

I want the output in the following manner:

from_user| to_user | message |
241 | 226 | How are you? |
241 | 256 | Hi test message |

I have tried this query:

SELECT * FROM `messages` where from_user=241 group by to_user order by created DESC

I am getting the following output which is wrong:

enter image description here

Thanks in advance.

Answer Source

Try his:

SELECT t1.* 
FROM `messages` AS t1
   SELECT to_user, MAX(created) AS created
   FROM `messages` 
   WHERE from_user=241
   GROUP BY to_user
) AS t2 ON t1.to_user = t2.to_user AND t1.created = t2.created
WHERE from_user=241
ORDER BY t1.created DESC