mysql - 选择表中的最新记录(日期时间字段)

Question

我已经搜索了该网站以寻求帮助，但仍在苦苦挣扎。这是我的 table :

messages========idthread_iduser_idsubjectbodydate_sent

Basically I want to retrieve the latest record for each thread_id. I have tried the following:

SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages
WHERE user_id=1 AND date_sent=(select max(date_sent))
GROUP BY thread_id
ORDER BY date_sent DESC

但它给了我最旧的记录，而不是最新的!

谁能给点建议？

编辑:表转储:

---- Table structure for table `messages`--CREATE TABLE IF NOT EXISTS `messages` (  `id` int(10) unsigned NOT NULL auto_increment,  `thread_id` int(10) unsigned NOT NULL,  `user_id` int(10) unsigned NOT NULL,  `body` text NOT NULL,  `date_sent` datetime NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;---- Dumping data for table `messages`--INSERT INTO `messages` (`id`, `thread_id`, `user_id`, `body`, `date_sent`) VALUES(1, 1, 1, 'Test Message', '2011-01-20 00:13:51'),(2, 1, 6, 'Test Message', '2011-01-20 01:03:50'),(3, 1, 6, 'Test Message', '2011-01-20 01:22:52'),(4, 1, 6, 'Test Message', '2011-01-20 11:59:01'),(5, 1, 1, 'Test Message', '2011-01-20 11:59:22'),(6, 1, 6, 'Test Message', '2011-01-20 12:10:37'),(7, 1, 1, 'Test Message', '2011-01-20 12:10:51'),(8, 2, 6, 'Test Message', '2011-01-20 12:45:29'),(9, 1, 6, 'Test Message', '2011-01-20 13:08:42'),(10, 1, 1, 'Test Message', '2011-01-20 13:09:49'),(11, 2, 1, 'Test Message', '2011-01-20 13:10:17'),(12, 3, 1, 'Test Message', '2011-01-20 13:11:09'),(13, 1, 1, 'Test Message', '2011-01-21 02:31:43'),(14, 2, 1, 'Test Message', '2011-01-21 02:31:52'),(15, 4, 1, 'Test Message', '2011-01-21 02:31:57'),(16, 3, 1, 'Test Message', '2011-01-21 02:32:10'),(17, 4, 6, 'Test Message', '2011-01-20 22:36:57'),(20, 1, 6, 'Test Message', '2011-01-20 23:02:36'),(21, 4, 1, 'Test Message', '2011-01-20 23:17:22');

编辑:抱歉-我可能在这里有些困惑-基本上我想要的是检索给定 user_id 的所有消息，然后从这些检索到的消息中找到最新消息(每个 thread_id)。

Answer 1

SELECT 
    id, thread_id, user_id, subject, body, date_sent
FROM
    messages
WHERE
    date_sent IN (SELECT 
            MAX(date_sent)
        FROM
            messages
        WHERE
            user_id = 6
        GROUP BY thread_id)
ORDER BY thread_id ASC, date_sent DESC;

让我知道它现在是否有效