选择表中的最新记录(日期时间字段) [英] Select latest record in table (datetime field)

问题描述

我已经搜索了该站点以寻求帮助，但仍在努力.这是我的桌子:

I have searched the site for assistance but still struggling. Here is my table:

messages
========
id
thread_id
user_id
subject
body
date_sent

基本上，我想检索每个thread_id的最新记录.我尝试了以下方法:

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

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

BUT it is giving me the oldest records, not the newest!

有人能提供建议吗?

表转储:

--
-- 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).

Apologies - I may have got things slightly confused here - basically what I want is to retrieve all messages for a given user_id, THEN find the latest message (per thread_id) from those retrieved messages.

推荐答案

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;

让我知道它现在是否有效

Let me know if it works now

这篇关于选择表中的最新记录(日期时间字段)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！