kyawzintun kyawzintun -5 years ago 103
MySQL Question

Mysql Compare three datetime fields

I want to get

from a database that is between 3 dates field.
Now I am using the following query but it is not ok for my requirement.
How to compare 3 date field to get

SELECT DISTINCT, MAX( cc.updated ) AS clinicComment_date, MAX( af.created ) AS attenderInfo_created, e.updated AS event_updated,
WHEN MAX( cc.updated ) > MAX( af.created )
THEN MAX( cc.updated )
WHEN MAX( cc.updated ) < MAX( af.created )
THEN MAX( af.created )
ELSE e.updated
) AS latest_date
FROM Event e
LEFT JOIN EventClinic ec ON = ec.event_id
LEFT JOIN ClinicComment cc ON ec.clinic_id = cc.clinic_id
LEFT JOIN AttendersInfo af ON af.event_id =
WHERE e.status = 'active'
AND ( =43
OR =45
GROUP BY e.title
ORDER BY latest_date DESC


Answer Source

I think you can use GREATEST() function

GREATEST(cc.updated, af.created, e.updated) AS latest_date

THis one should give you the latest date for a single row

If you want the latest between all the rows:

MAX(GREATEST(cc.updated, af.created, e.updated)) AS latest_date

that's the same of doing

GREATEST(MAX(cc.updated), MAX(af.created), MAX(e.updated)) AS latest_date

To deal with NULL values you can use COALESCE

    COLAESCE(MAX(cc.updated), 0), 
    COLAESCE(MAX(af.created), 0),  
    COLAESCE(MAX(e.updated), 0) 
) AS latest_date


        COLAESCE(cc.updated), 0), 
        COLAESCE(af.created), 0),  
        COLAESCE(e.updated), 0) 
)) AS latest_date

the result should be the same, maybe they differ in performance, I don't know

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