CODEWITHSUNDEEP
phpmysqlsql
Gumbo
Gumbo

Reputation: 201

Select one row for each month mysql

I'm new to MySQL and I'm trying to select one row for each month and year in the table. I'm making a blog site and need to echo out the months so people can press particular month of year and get all the blog posts written that month.

Here is what I started with but it's not working right.

$query = "SELECT * FROM messages 
                    WHERE date in 
                    (select DISTINCT max(date) from messages 
                    GROUP BY YEAR(date), MONTH(date))";

Thanks

Upvotes: 1

Views: 1106

Answers (1)

Marc B
Marc B

Reputation: 360702

No need for a subselect:

SELECT MONTH(date), YEAR(date)
FROM messages
GROUP BY MONTH(date), YEAR(date)

That'll give you each year/month combo which has a message listed in your table.

Then you can pull up the messages later on with

SELECT *
FROM messages
WHERE MONTH(date) = $selected_month AND YEAR(date) = $selected_year

Upvotes: 3

Related Questions