0

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..!

Konerak
  • 39,272
  • 12
  • 98
  • 118
Tim
  • 6,986
  • 8
  • 38
  • 57
  • This is a classic question in SQL, called the "greatest n per group". Your example is the easiest case, the "greatest 1 by group". Read http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column and see if you can solve it, if not, post your problems and we'll help. – Konerak Sep 12 '13 at 09:16
  • Interesting, thanks @Konerak I'll see what I can do :\ – Tim Sep 12 '13 at 09:18
  • I've gone as far as I understand `SELECT messages.* FROM messages t1 LEFT OUTER JOIN users t2 ON t1.from = t2.id AND ((t1."Date" < t2."Date") OR (t1."Date" = t2."Date" AND t1.id < t2.id)) WHERE t2.UserId IS NULL;` – Tim Sep 12 '13 at 09:21
  • I'm not comparing two tables with dates etc? – Tim Sep 12 '13 at 09:22

1 Answers1

0

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`);
Ganesh Jadhav
  • 2,830
  • 1
  • 20
  • 32
  • This looks like the closest so far! Let me try... – Tim Sep 12 '13 at 09:37
  • That's awesome :D Thanks man, I've changed the 'date' to ID though as the ID will always be the latest. How would I adapt this to include User info where message.from = user.id? – Tim Sep 12 '13 at 09:39
  • Magic. Thanks for your help, you're the only person from about 60 who has understood and managed to do this.. – Tim Sep 12 '13 at 09:48
  • this is not good cause it should Group by from or to. Image that a user just send a message to specific user. so what happens? so you had to check WHERE `from` = $currentUserID – Mahdi Mar 07 '17 at 14:04