SQL - Selecting the latest entry that meets a condition, more complicated than it sounds-CodePudding

So basically i have a database table that stores messages between users. It has two columns; one is named "to", the other is named "from" which indicates who sent and who received. I'm trying to make a vertical list of conversations for user to choose from in order to send a message to a person. Basically the same system that Whatsapp has. The most recent conversation appears on top. And the one below it is the second most recent and the last item in the list is the oldest conversation. I assume you get the idea. I'm trying to find out how i can write a SQL command to retrieve the latest messages from different users and sort them from newest to oldest. Any help is appreciated.

CodePudding user response：

If you update with your table schema I will write the query for you?

basically you want to

select from messages where to = current user or from = currentuser and then GROUPBY to, from to get unique results then ORDER BY datetime DESC

Something like this, if u share your schema I can write it out proper and test this was just a quickie thought

SELECT * FROM messages WHERE `to` = 1 or `from` = 1
GROUP BY `to`, `from`
ORDER BY `created_at` DESC;