Search code examples
mysqlmaxdate

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.

Solution

  • 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.