Reputation: 1693
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
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