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

Posted by Yako malin on Stack Overflow See other posts from Stack Overflow or by Yako malin
Published on 2010-04-29T06:59:56Z Indexed on 2010/04/30 16:27 UTC
Read the original article Hit count: 335

Filed under:
|
|
|

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) use 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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about select