我有一个mysql查询，我在排序发件人的最新消息时遇到问题下面是我的问题：
SELECT
`Mes`.`fromid`,
`Mes`.`is_read`,
`Mes`.`id` AS `mesid`,
`Mes`.`message`,
max(Mes.date) AS `date`,
`User`.`username`,
`User`.`MemberID` AS `Uid`
FROM `messages` AS `Mes`
INNER JOIN `users` AS `User` ON User.MemberID = Mes.fromid
WHERE (Mes.toid = 5 AND Mes.fromid <> 5)
GROUP BY `Mes`.`fromid`
ORDER BY `date` DESC
这是我在数据库中的表：用户表
MemberID UserName Email Password
1 User1 user1@gmail.com 123456
2 User2 user2@gmail.com 123456
3 User3 user3@gmail.com 123456
4 User4 user4@gmail.com 123456
5 User5 user5@gmail.com 123456
消息表：
id fromid toid message is_read date
1 5 2 hello test 1 1 2012-08-24 01:00:00
2 2 5 hello test 2 1 2012-08-24 02:00:00
3 3 5 hello test 3 1 2012-08-24 03:00:00
4 4 5 hello test 4 1 2012-08-24 04:00:00
5 2 5 hello test 5 1 2012-08-25 05:00:00
以及我的查询结果：
SELECT
`Mes`.`fromid`,
`Mes`.`is_read`,
`Mes`.`id` AS `mesid`,
`Mes`.`message`,
max(Mes.date) AS `date`,
`User`.`username`,
`User`.`MemberID` AS `Uid`
FROM `messages` AS `Mes`
INNER JOIN `users` AS `User` ON User.MemberID = Mes.fromid
WHERE (Mes.toid = 5 AND Mes.fromid <> 5)
GROUP BY `Mes`.`fromid`
ORDER BY `date` DESC
是：
USERNAME MESSAGE DATE
user2 hello test 2 2012-08-25 05:00:00
user4 hello test 4 2012-08-25 04:00:00
user4 hello test 3 2012-08-25 03:00:00
若你们已经注意到从日期开始排序是正确的，但最新的消息是不正确的。我想要这样的输出。
USERNAME MESSAGE DATE
user2 hello test 5 2012-08-25 05:00:00
user4 hello test 4 2012-08-25 04:00:00
user4 hello test 3 2012-08-25 03:00:00
我想对最新的消息进行排序，而不是消息"hello-test2"你好测试5"
有人能帮我解决问题吗？
非常感谢。。。
应该是这样的
SELECT * // -- select the columns you want
FROM Messages a
INNER JOIN
(
SELECT fromid, toid, MAX(`date`) maxDATE
FROM Messages
GROUP BY fromid, toid
) b ON a.fromID = b.fromID AND
a.toid = b.toid AND
a.`date` = b.maxDATE
INNER JOIN users c
ON c.MemberID = a.fromID
WHERE a.toid = 5 AND a.fromid <> 5
ORDER BY `date` DESC