MySQL: Request to select the last 10 send/received messages to/by different users

Question

I want to select the 10 last messages you received OR you sent TO different users.

For example the results must be shown like that: 

1. John1 - last message received 04/17/10 3:12 
2. Thomy - last message sent 04/16/10 1:26 
3. Pamela - last message received 04/12/10 3:51 
4. Freddy - last message received 03/28/10 9:00 
5. Jack - last message sent 03/20/10 4:53 
6. Tom - last message received 02/01/10 7:41 
..... 

Table looks like:

CREATE TABLE `messages` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`sender` int(11) DEFAULT NULL, 
`receiver` int(11) DEFAULT NULL, 
`content` text 
) 

I think Facebook (and the iPhone) uses this solution. When you go to your mail box, you have the last messages received/sent grouped by Users (friends).

So I will take an example. If I have theses messages (THEY ARE ORDERED YET):

**Mike**
**Tom**
**Pam**
Mike
Mike
**John**
John
Pam
**Steve**
**Bobby**
Steve
Steve
Bobby

Only Message with ** should be returned because they are the LAST messages I sent/received By User.

In fact I want the last message of EACH discussion.

What is the solution?

Answer 1

The question is somewhat unclear. If you simply want the last 10 messages that involve user #12345, simply use:

SELECT   *
FROM     messages
WHERE    sender = 12345 OR receiver = 12345
ORDER BY time DESC
LIMIT    10

Update to match your updated requirements. Not the most elegant, but works as long as no user sends two messages at the same second.

SELECT   m1.*
FROM     messages m1
         INNER JOIN (SELECT   MAX(time) AS time,
                              IF(receiver = 12345, sender, receiver ) AS user
                     FROM     messages
                     GROUP BY user) m2
           ON m1.time = m2.time AND (m1.sender = m2.user OR m1.receiver = m2.user)
WHERE    sender = 12345 OR receiver = 12345
ORDER BY m1.time DESC

Answer 2

Your query is pretty good but I only get the 10 last messages I received or I sent.

But I want to GROUP them BY users/sender.

Answer 3

Perhaps you're looking for GROUP BY, although having separate fields for Sender and Receiver makes it harder.

IMHO it would be better to have UserID and MessageDirection SET('IN','OUT' ).

Then you could make

SELECT UserID, MAX(time) GROUP BY UserID

Edit

If you want last 10

SELECT UserID, MAX(time) GROUP BY UserID ORDER BY time LIMIT 10

It's probably not the fastest solution and can be optimized.

Answer 4

I think I have found a solution using Max Shawabkeh tips. But in fact I think I can optimize it.

SELECT *
FROM 

(
SELECT *
FROM messages
ORDER BY sendtime DESC
) messages2

WHERE receiver = @id_user
OR sender = @id_user
GROUP BY IF( receiver = @id_user, sender, receiver ) 

I think there is a better solution ....