Select latest record in table (datetime field)

Question

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

 messages ======== id thread_id user_id subject body date_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 

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

Anyone able to advise?

EDIT: Table dump:

 -- -- 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'); 

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.

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; 

Let me know if it works now