0

I have a table to store chats between users of my website. The table structure is as shown below :

id | from_user_id | to_user_id | text               | viewed
---+--------------+------------+--------------------+---------
1  |    1         |     2      | Hello              |    0
2  |    2         |     1      | Hi, how are you    |    0
3  |    1         |     2      | lets party         |    0
4  |    1         |     3      | What's Up Bro ?    |    0
5  |    1         |     3      | How is Life ?      |    0

I want to write a query to select all the rows for a particular from_user_id and all the possible to_user_id with the latest row entered in the table.so if i want want all messages where from_user_id = 1, my output for the above data should be like this :

What i Want:

id | from_user_id | to_user_id | text               | viewed
---+--------------+------------+--------------------+---------
3  |    1         |     2      | lets party         |    0
5  |    1         |     3      | How is Life ?      |    0

What I have Tried :

I have tried using Group By with Order By and my Query is something like this :

select * from `messages` where `from_user_id` = 1 group by `to_user_id` order by `id` desc

however the above Query gives me the first record for the to_user_id and not the last one like this :

What I Get :

id | from_user_id | to_user_id | text               | viewed
---+--------------+------------+--------------------+---------
1  |    1         |     2      | Hello              |    0
4  |    1         |     3      | What's Up Bro ?    |    0
Mohan
  • 4,677
  • 7
  • 42
  • 65
  • This question is asked and answered endlessly, which is why I've closed it as a duplicate. The answers provided in the duplicate aren't the best, but better answers can be found right over there ---------> – Strawberry Aug 16 '16 at 11:07
  • What you are triing does not work. Order by only orders the result after Group by – Jens Aug 16 '16 at 11:07
  • is there any way to do this ? – Mohan Aug 16 '16 at 11:08
  • @Strawberry can you please provide a link where i can find the answer. – Mohan Aug 16 '16 at 11:09

0 Answers0