How to select the rows with maximum timestamp for each month?

Question

How can I select the maximum timestamp for each month?

Welcome, to improve your experience on SO please [take the tour](http://stackoverflow.com/tour) and read [how to ask](https://stackoverflow.com/help/how-to-ask), an [On Topic question](https://stackoverflow.com/help/on-topic), then look at the [Question Check list](https://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist), the [perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) and how to create a [Minimal, Complete and Verifiable Example](http://stackoverflow.com/help/mcve) — RiggsFolly, Sep 02 '21 at 12:12
Please, in future, don't use screengrabs to show data. Include the text of the data in your question indented four spaces. — O. Jones, Sep 02 '21 at 12:29
I`m sorry, I should have explained the situations well. I will edit it later. — hiro, Sep 02 '21 at 13:15

score 0 · Answer 1 · answered Sep 02 '21 at 12:18

0

I think this Query will do the job.

SELECT 
YEAR(START_DATE), 
MONTH(START_DATE), 
MAX(IFNULL(END_DATE, '9999-12-31 23:59:59'))
GROUP BY YEAR(START_DATE), MONTH(START_DATE)

answered Sep 02 '21 at 12:18

aaring

Why START_DATE, and not END_DATE, or, for example, a middle between them? – Akina Sep 02 '21 at 12:21
From the example data in the question I have concluded that the month is defined via START_DATE. – aaring Sep 02 '21 at 15:57

score 0 · Answer 2 · answered Sep 02 '21 at 12:28

0

This will work for you, fairly efficiently given the correct indexes.

SELECT LAST_DAY(start_date) month_ending,
       MAX(end_date) max_end_date
  FROM tbl
 GROUP BY LAST_DAY(start_date)

answered Sep 02 '21 at 12:28

O. Jones

I`m sorry, I mean I want to select the rows that have the latest record for each month. I thought i could use the "LAST_DAY" but it only returns the last day of each month – hiro Sep 02 '21 at 13:22

2 Answers2