Max(Date) problem

[Sid3335]

Hi:

I have 2 records in my database:

they have the following fields:
EnqID, Actions, Date

the EnqID is the same (1), the actions and date are different.

i want to return the Actions and Date. But only on the latest date.

so i tried:

SELECT Actions,
Max(STR_TO_DATE(`Date`,'%d/%m/%Y')) AS `lastDate`
FROM Progress
WHERE EnqID = 1
Group By EnqID


this gives me the correct date that i want but not the action, it is in fact the date from the second record and the action from the first.
I've tried many variations but i can't get it to work.

Can anyone tell me the problem? Thanks Guys

[Sid3335]

ok i think i got it (finally!!!)


SELECT
Actions,
STR_TO_DATE(`Date`,'%d/%m/%Y') as lastDate
FROM
Progress
WHERE EnqID = " . $enqID . "
AND STR_TO_DATE(`Date`,'%d/%m/%Y') IN
(SELECT
Max(STR_TO_DATE(`Date`,'%d/%m/%Y'))
FROM
Progress
WHERE EnqID = " . $enqID . ")"


Thanks anyway guys

[chazzy]

if you stop storing your dates as varchar columns and start using the date/time types, your sql would look so much cleaner.

[Sid3335]

I agree and have changed it to a date/time.