title: Select latest record in table (datetime field)
body: <p>I have searched the site for assistance but still struggling. Here is my table:</p>
  
  <pre>
  messages
  ========
  id
  thread_id
  user_id
  subject
  body
  date_sent
  </pre>
  
  <p>Basically I want to retrieve the latest record for each thread_id. I have tried the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>BUT it is giving me the oldest records, not the newest!</p>
  
  <p>Anyone able to advise?</p>
  
  <p>EDIT: Table dump:</p>
  
  <pre>
  --
  -- 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');
  </pre>
  
  <p>EDIT: 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.</p>