How to get last date for each month

Question

As title says, I want to select one record from each month with the latest date. I have a table like this:

ID  field1          field2          created_at
----------------------------------------------
1   40000.0000      70000.0000      2018-01-31
2   80000.0000      90000.0000      2018-02-28
3   10000.0000      30000.0000      2018-03-31
4   12000.0000      32000.0000      2018-04-30
5   212500.0000     312500.0000     2018-05-31
6   324000.0000     424000.0000     2018-06-30
7   122300.0000     222300.0000     2018-07-26
8   357772.2420     307600.9081     2018-07-31

I came up with this query, but it's not working as expected.

SELECT id, field1, field2, MAX(created_at) AS created_at FROM my_table GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY created_at ASC

The output I'm getting:

ID  field1          field2          created_at
----------------------------------------------
1   40000.0000      70000.0000      2018-01-31
2   80000.0000      90000.0000      2018-02-28
3   10000.0000      30000.0000      2018-03-31
4   12000.0000      32000.0000      2018-04-30
5   212500.0000     312500.0000     2018-05-31
6   324000.0000     424000.0000     2018-06-30
7   122300.0000     222300.0000     2018-07-31   <-- values from ID 7 but date from ID 8

Expected output

ID  field1          field2          created_at
----------------------------------------------
1   40000.0000      70000.0000      2018-01-31
2   80000.0000      90000.0000      2018-02-28
3   10000.0000      30000.0000      2018-03-31
4   12000.0000      32000.0000      2018-04-30
5   212500.0000     312500.0000     2018-05-31
6   324000.0000     424000.0000     2018-06-30
8   357772.2420     307600.9081     2018-07-31

How can I select values from the same record as MAX(created_at)?

SQLFiddle

What else do you expect, Yuri? When you group by and use an aggregate function like max on a column, that column's value will be controlled by the function. But the columns you don't otherwise aggregate will pick the first value. In other words, by telling MySQL to select the max of created_at, you don't also automatically tell it to use the max of all other columns use select. — marekful, Jul 26 '18 at 12:46
Well, the obvious when you understand this is to use max() for all other columns, but I appreciate that's not clean. It would be nice if MySQL could have an extension that would allow to select a row from a group where _expression_. — marekful, Jul 26 '18 at 12:52

score 2 · Accepted Answer · answered Jul 26 '18 at 12:32

2

You could try this:

SELECT id, field1, field2, created_at AS created_at 
FROM `my_values` 
where created_at in (select max(created_at)
                 FROM `my_values` 
 GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY created_at ASC )

See SQLFiddle

answered Jul 26 '18 at 12:32

dev8080

3,950
1
12
18

That works, thanks. I wonder if there is a more elegant way to achieve the same result.. – Yuri Jul 26 '18 at 12:34

Zaynul Abadin Tuhin · Answer 2 · 2018-07-26T12:44:15.933

by using join

select id, field1, field2,T2.created_at from my_values T1 
inner join 
(
select max(created_at) as created_at
                 FROM `my_values` 
 GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY created_at ASC
 ) as T2
 on T1.created_at=T2.created_at

http://sqlfiddle.com/#!9/4f7442/18

id  field1  field2  created_at
1   40000   70000   2018-01-31
2   80000   90000   2018-02-28
3   10000   30000   2018-03-31
4   12000   32000   2018-04-30
5   212500  312500  2018-05-31
6   324000  424000  2018-06-30
8   357772.242  307600.9081 2018-07-31

How to get last date for each month

2 Answers2