Some DATE problems

[AdamWalton]

Hello,

MySQL version 3.23.56

I have a table, called blog, with the following structure:

blog_id - UNSIGNED INT AUTO INC PRIMARY INDEX
blog_date - DATE INDEX
blog_text - FULLTEXT
blog_title - VARCHAR

I want to be able to list the table entries grouped by year, then month, then day... something like this... it's simply to create a list of hyperlinks to archived posts.

2005
October
9th
8th
7th

July
31st
5th
2nd

2004
December
31st
29th

In other words I want to be able to group the table entries by year, then month, then day... I thought that one way to do this would be to nest the queries to extract the unique months, and then days, within a query to extract the unique years...

SELECT DISTINCT DATE_FORMAT(blog_date, '%Y') AS blog_year FROM blog GROUP BY blog_year DESC

That works fine for getting the unique years, but I don't know how to construct a query to then select the unique months [in date order, as opposed to alphabetical order] from that unique year...

Can anybody help?

Thank you for your time.

[nemonoman]

SELECT DATE_FORMAT(blog_date, '%Y%m') AS blog_yearmonth FROM blog GROUP BY 1 DESC

PS you don't need "DISTINCT" if you use GROUP BY. The GROUP BY function defines that the returned elements will be unique.

You can also display a different value than that grouped by:

SELECT DISTINCT DATE_FORMAT(blog_date, '$M %Y) AS blog_yearmonth FROM blog GROUP BY DATE_FORMAT (blog_date, '%Y%m') order by DATE_FORMAT (blog_date, '%Y%m') DESC

[AdamWalton]

Many thanks.