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