CODEWITHSUNDEEP
mysqlmaxdate
karan
karan

Reputation: 29

Select records according to month's last day

I have table having 26 columns in which first 3 Columns are day,month,year. And rest of columns having some information that i have to show. Now i have to fetch records according to month's last day. I have tried writing code.

select * from subscription_stats where year * 10000 + month * 100 + day = LAST_DAY(CONCAT(year,'-',month,'-',day))

But this will fetch records from last day of every month. When i dont have actual last day in records then this code will not work. So instead of LAST_DAY i want some functionality like MAX date in that month. How can i implement this functionality.

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want the last date in each month in your data. For this:

select s.*
from subscription_stats s
where s.day = (select max(s2.day)
               from subscription_stats s2
               where s2.year = s.year and s2.month = s.month
              );

Although it would not make this query much simpler, you should be storing dates as dates in your table. That is, one date, not three separate columns for year/month/day.

Upvotes: 1

Related Questions