I have a simple question related to grouping rows by date with some "narrative" periods. Let's assume that I have very simple table with articles. ID which is PK, title and date. The date column is datetime / timestamp.
I would like to group somehow my results so I can present them in the view like
Written in last 7 days:
Written in last 30 days:
Can I achieve that in just one single query with some group by statements?
Best How To :
Gordon should have credit for writing this out. But I think you probably just want to append a column with the appropriate descriptor and probably sort them in the order you'd like to see them.
case when date = curdate() then 'today'
when date >= curdate() - interval 6 day then 'last 7 days'
when date >= curdate() - interval 29 day then 'last 30 days'
end as bucket,
when date = curdate() then 1
when date >= curdate() - interval 6 day then 2
when date >= curdate() - interval 29 day then 3
It looks like you didn't have the titles in alphabetical order. If you want them sorted by age then remove the case expression and just use the date value.