MySQL: Request to select the last 10 send/received messages to/by different users
- by Yako malin
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?
