Marium Malik Marium Malik - 1 year ago 144
MySQL Question

MySQL query to get that row which has the last day of first month of date field

In my database I have a field named DateLastSaved:
Suppose the values are:

1. 2016-05-12 08:07:00,
2. 2016-05-22 09:06:00,
3. 2016-05-22 09:06:00,
4. 2016-06-13 09:00:00,
5. 2016-06-13 09:00:00

I wan't such query that would return me that row whose DateLastSaved field has the minimum month, in above case "5" and the maximum date of that month, which is 2, 3, but my query should return one result, i.e either 2 or 3.

I am using the following query:

SELECT MIN(LAST_DAY(DateLastSaved))FirstMonth
FROM InitialLog
WHERE FileName='Dr. Adam Kotowski Patient Names.doc'

But it is returning me the first date, that is, minimum, not the maximum one. Any suggestions?

Answer Source

Try this:

FROM InitialLog
WHERE MONTH(DateLastSaved) = (SELECT MIN(MONTH(DateLastSaved)) FROM InitialLog)

Demo here