MySQL maximum date before a given date
I have a table that has a series of dates along with an ID:
MY_ID MY_DATE
A 3/6/1987 12:00:00 AM
A 4/1/1988 12:00:00 AM
A 4/28/1989 12:00:00 AM
B 3/6/1987 12:00:00 AM
B 4/1/1988 12:00:00 AM
I would like to find the maximum A that is before say 04/27/1988.
This does not work, and I cant figure out why:
Select max(My_date) from Mytable
where MY_ID = 'A'
and My_date < str_to_date('04/27/1988')
group by MY_ID
it always just gives me the max() record
MY_ID MY_DATE
A 3/6/1987 12:00:00 AM
A 4/1/1988 12:00:00 AM
A 4/28/1989 12:00:00 AM
B 3/6/1987 12:00:00 AM
B 4/1/1988 12:00:00 AM
I would like to find the maximum A that is before say 04/27/1988.
This does not work, and I cant figure out why:
Select max(My_date) from Mytable
where MY_ID = 'A'
and My_date < str_to_date('04/27/1988')
group by MY_ID
it always just gives me the max() record
Sharath S
I did not see any issue. What is the date are you getting with your query?
ASKER
I'm trying to get the second record as presented above:
A 4/1/1988 12:00:00 AM
and I keep getting :
A 4/28/1989 12:00:00 AM
almost like the Max() takes precedence
A 4/1/1988 12:00:00 AM
and I keep getting :
A 4/28/1989 12:00:00 AM
almost like the Max() takes precedence
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.