CODEWITHSUNDEEP
mysql
Sarah Mandana
Sarah Mandana

Reputation: 1513

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?

Upvotes: 2

Views: 32

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

Try this:

SELECT *
FROM InitialLog
WHERE MONTH(DateLastSaved) = (SELECT MIN(MONTH(DateLastSaved)) FROM InitialLog)
ORDER BY DAY(DateLastSaved) DESC LIMIT 1

Demo here

Upvotes: 1

Related Questions