mysql - getting the last 10 unique months

Question

I want to get the last 10 unique months where there have been events, stored as event_date as a mysql date. I am using coldfusion so could group the result but can't get that working as I would like.

This is what I have so far.

SELECT MONTH(event_date) AS month, YEAR(event_date) AS year, event_date from events

so ideally if there were no posts in August would be output as

september 2010 july 2010 june 2010 may

etc etc

Thanks.

R.

Answer 1

SELECT DISTINCT MONTH(event_date) AS month, YEAR(event_date) AS year from events ORDER BY year DESC, month DESC LIMIT 10;