mysql date time select

[Divante]

---------+-----------------------------------------------------

Table  | Create Table

---------+-----------------------------------------------------

records | CREATE TABLE `records` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`User_id` int(11) NOT NULL DEFAULT '0',

`First_Name` varchar(20) DEFAULT NULL,

`Last_Name` varchar(25) DEFAULT NULL,

`Product` enum('flowers', 'vases', 'ornaments', 'draperies' ,

`Create_Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

ENGINE=MyISAM DEFAULT CHARSET=latin1 |

 

 

Basically im stumped on how to get the user id from the table but only for those users whose max create date within the range of  3month and  a month ago from the current time.That is i only want records for those users whose last purchase was a month nad up to 3 months ago.Any kind of help will be appreciated.

[PFMaBiSmAd]

SELECT User_id, MAX(Create_Date) as max_date FROM records GROUP BY User_id HAVING DATE(max_date) BETWEEN DATE_SUB(CURDATE(),INTERVAL 3 MONTH) AND DATE_SUB(CURDATE(),INTERVAL 1 MONTH)

[PFMaBiSmAd]

You can make the above query a little more efficient by throwing in a WHERE clause so that only records greater-than or equal to three months ago are used.

[Divante]

Thanks very much exactly what i needed.