Marium Malik Marium Malik - 2 years ago 177
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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download