mysql: Get last conversation records by user

Question

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:

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:

Thanks in advance.

Why are you grouping by to_user? You will have to aggregate the fields in your select if you do so. You could use group_concat() to concat the different messages sent. But when grouping, the order doesn't make a lot of sense, because the grouped entries have different dates, so you would have to specify if you want to group by the newest or oldest message of the different groups. — Philipp, Jun 09 '16 at 07:45

score 5 · Accepted Answer · answered Jun 09 '16 at 07:49

5

Try his:

SELECT t1.* 
FROM `messages` AS t1
JOIN (
   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

answered Jun 09 '16 at 07:49

Giorgos Betsos

71,379
9
63
98

awesome!!! worked like a charm. thank you so much – Amuk Saxena Jun 09 '16 at 08:20
Working fine. But cannot get the last message if to_user sent a message. – Silambarasan Jul 31 '19 at 09:51

score 2 · Answer 2 · answered Jun 09 '16 at 07:49

2

SELECT *
FROM
  (SELECT *
   FROM `messages`
   WHERE from_user=241
   ORDER BY created DESC) as test
GROUP BY to_user

answered Jun 09 '16 at 07:49

Sadikhasan

18,365
21
80
122

1

Should add LIMIT 1 in the Subquery to return only the latest entry. – Philipp Jun 09 '16 at 08:07
Its ok without LIMIT – Sadikhasan Jun 09 '16 at 08:12

Wasiq Muhammad · Answer 3 · 2016-06-09T07:48:36.950

1

Try this

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

edited Jun 09 '16 at 07:48

answered Jun 09 '16 at 07:45

Wasiq Muhammad

3,080
3
16
29

thanks, i need one record per to_user... how to achieve this? – Amuk Saxena Jun 09 '16 at 07:46
1

No need to order by the created-date as the id already displays the chronology I'd think – Philipp Jun 09 '16 at 07:48

Philipp · Answer 4 · 2016-06-09T08:06:23.530

1

If you want all posts of the from_user in one field (misunderstood the question I think):

SELECT group_concat(messages.message) 
    FROM messages 
    WHERE from_user = 241 
    GROUP BY to_user 
    ORDER BY MAX(id) DESC

You have to say by which criteria you want it to be ordered though. You can have the starting of the conversation or last message of the conversation.

edited Jun 09 '16 at 08:06

answered Jun 09 '16 at 07:54

Philipp

2,787
2
25
27

mysql: Get last conversation records by user

4 Answers4

Linked