get all months last days in mysql

For example I have a table with fields:

id     date
1      2001-01-01
2      2001-01-05
.................
N      2011-12-31

How get i get all months last days from this table? example:

if i have dates like 2001-05-31 and 200开发者_如何学C1-06-01 i need only 2001-05-31 not both

You can do SELECT LAST_DAY for example the below returns Oct. 31st. 2010

SELECT LAST_DAY('2010-10-10');

select max(date_format(date, '%d')) as last_day_of_the_month
from table
group by date_format(date, '%Y%m')

maybe this would work better?

select DISTINCT(LAST_DAY(date)) from table GROUP BY date_format(date, '%Y%m')

SELECT id, date
FROM  `table` 
WHERE DATE( date ) = LAST_DAY( date ) 

The DATE function over field is for filter the date without time, use only if you have a datetime column.

The query get all rows with date = last day of month.

select subdate(adddate(subdate(`date`, day(`date`) - 1), interval 1 month), 1)

Note: This is the "hard way". See @harper89's answer - it's better :)

I found a solution. but this query is very slow on large tables. so I am still looking for a better solution

select DISTINCT(LAST_DAY(date)) from table;

select max(date)
from table
group by year(date), month(date)