Search code examples
mysqlsqlgreatest-n-per-group

SQL for message inbox

I have an inbox of messages for my users, I have a 'messages' table and a 'users' table. Messages table has to and from fields which contain the user IDs.

I want to select the latest message from every user, where the to field is the current user ID, i.e.

"select (latest Message, by Message.ID) from (unique users) where Message.to = $currentUserID (and left join User where UserID = Message.from)"

I want to end up with something like this:

http://www.innerfence.com/blog/wp-content/uploads/screenshot-iphone-inbox-thumb.png

I can't figure out the query I need for this, please help..!

Solution

  • Try this. It is tested and working fine. Updated to include information about the sender. 

    SELECT * FROM `messages` tm LEFT JOIN `users` tu ON `tm.from` = `tu.userid`
WHERE `tm.date` IN
    (SELECT MAX(`date`) FROM `messages` WHERE `to` = $currentUserID GROUP BY `from`);