CODEWITHSUNDEEP
mysqlsql
saperlipopette
saperlipopette

Reputation: 1693

SQL - Select latest messages on a chat table

i'm currently trying to make an online chat with Ajax and the messages are sent via a php file to my database. I would like to be able to list all the conversations where a user is involved. Let's take this as an example : exemple of table

I would like to list the conversations ordered from the most recent one to the oldest.

I should have conversation with user 25846.... since it's the last message, then with 10206...... and finally with 11627.....

My desired output is a table of 3 records with the fields ID_to and content_msg, order as above. This is the current SQL request i'm trying : 

SELECT DISTINCT ID_to 
FROM (
    SELECT * 
    FROM messages 
    WHERE ID_to = 1140816689329214 OR ID_from = 1140816689329214 
    ORDER BY timestamp_msg DESC
    ) as tmp

Even with this the output is not in the right order, and I can't understand why since I have a order by on my request.

Thank you in advance for any help

Upvotes: 1

Views: 1324

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Ordering is determined by the ORDER BY in the outermost query. Ordering in subqueries doesn't matter.

This would seem to be what you want:

SELECT id_to
FROM messages 
WHERE ID_to = 1140816689329214 OR ID_from = 1140816689329214 
GROUP BY id_to
ORDER BY MAX(timestamp_msg) DESC;

Upvotes: 1

Related Questions