How do I select using GROUP BY, ORDER BY. based on the date_time which is groupe

[jasonc]

SELECT * FROM message WHERE field = '100' AND post_time LIKE '%Y-%m-%d' GROUP BY post_time ORDER BY id ASC

 

I get no results show up using the above code.

 

all entries have a different date in yyyy-mm-dd format.  there are many entries that have '100' in the 'field'

 

What I am after is all entries where the 'field' is 100 and in date order but only have one entry for each date. and for the list to be ordered by the 'id' field.

 

can anyone suggest what query would do this.

 

 

EDIT:

 

i just realised that i will need both the original post_time and the reformatted post_time

 

i have been playing around with the code and now have this but still no results.

 

SELECT id, field, date_format(post_time, '%Y:%m-%d') as post_time, post_time as full_post_time FROM table WHERE field = '100' AND post_time LIKE '%Y-%m-%d' GROUP BY post_time ORDER BY id ASC

 

 

also just noticed that i did not say that i have stored the date string as yyy-mm-dd hh:mm:ss

[jasonc]

the ID field is unique and the post_time is formatted like...

 

2010-04-26 20:00:00

 

so the group by needs to be done using the first part of the post_time field not the full text group.

 

my table is..

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(11) NOT NULL auto_increment,
  `field` int(11) NOT NULL default '0',
  `message` text,
  `post_time` datetime default NULL,
  PRIMARY KEY  (`message_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=187 ;

INSERT INTO `table` (`id`, `field`, `message`, `post_time`) VALUES
(182, 10, 'message 5', '2010-04-25 00:20:07'),
(183, 10, 'message 2', '2010-04-25 00:20:09'),
(184, 10, 'message 1', '2010-04-26 08:06:53'),
(185, 10, 'message 4', '2010-04-26 08:07:12'),
(186, 10, 'message 3', '2010-04-26 08:07:47');

 

the results i would be looking for would be...

(182, 10, 'message 5', '2010-04-25 00:20:07'),
(184, 10, 'message 1', '2010-04-26 08:06:53'),

 

the first result for each date, the time would not be used for the grouping.

[fenway]

use date(post_time)?