Get last record of each month in MySQL....?

I have a problem in writing a query for MySQL. I have following fields in the DB

id     created_on            status
1      2011-02-15 12:47:09    1 
2      2011-02-24 12:47:09    1
3      2011-02-29 12:47:09    1
4      2011-03-11 12:47:09    1
5      2011-03-15 12:47:09    1
6      2011-03-22 12:47:09    1
7      2011-04-10 12:47:09    1
开发者_如何学Python8      2011-04-11 12:47:09    1

I need to select the last record of each month. That is for the month FEB record # 3 month MARCH record # 6 and for month APRIL record # 8

Please help me.....

Thanks in advance.....

SELECT * FROM table 
WHERE created_on in 
(select DISTINCT max(created_on) from table 
GROUP BY YEAR(created_on), MONTH(created_on))

Building off Dheer's answer:

SELECT r.*
FROM table AS r
    JOIN (
        SELECT MAX(t.created_on) AS created_on
        FROM table AS t
        GROUP BY YEAR(t.created_on), MONTH(t.created_on)
    ) AS x USING (created_on)

Be sure you have indexes on created_on, otherwise this query will kill your database if that table gets more than a couple hundred rows.

You first need to group by year and month (otherwise you'd be filtering out months in other years). Use MAX() to get the greatest date for each group.

SELECT *, MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on) 

Assuming that there is only one record for the day;

SELECT * from table where created_on IN (Select MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on) )