CODEWITHSUNDEEP
mysqlsql
James Buckland
James Buckland

Reputation: 59

SQL query for getting message users in order of last message

I have a SQL query that selects the users from my users table that have sent a message to or received a message from my selected user (senderID or recipientID in my messages table).

The query is as follows:

SELECT * 
FROM users 
WHERE (ID IN (SELECT DISTINCT(senderID) 
              FROM messages 
              WHERE recipientID = $userID) 
       OR ID IN (SELECT DISTINCT(recipientID) 
                 FROM messages 
                 WHERE senderID = $userID))

Given that the column sentAt is the timestamp of the message how can I order the return in order of which user sent the most recent message. I attempted to append the ORDER BY sentAt DESC to the subqueries but it did not return the expected result.

Any help would be greatly appreciated.

Upvotes: 1

Views: 510

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You seem to want a join instead of in:

select u.*
from users u join
     messages m
     on u.id in (m.senderid, m.recipientid)
where u.id = $userid
order by m.sentat desc

Upvotes: 2

Related Questions