Link to home
Start Free TrialLog in
Avatar of GNOVAK
GNOVAKFlag for United States of America

asked on

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
Avatar of Sharath S
Sharath S
Flag of United States of America image

I did not see any issue. What is the date are you getting with your query?
Avatar of GNOVAK

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
ASKER CERTIFIED SOLUTION
Avatar of GNOVAK
GNOVAK
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial