kyawzintun kyawzintun - 4 months ago 11
MySQL Question

Mysql Compare three datetime fields

I want to get

latest_date
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
latest_date
?

SELECT DISTINCT e.id, MAX( cc.updated ) AS clinicComment_date, MAX( af.created ) AS attenderInfo_created, e.updated AS event_updated,
(
CASE
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
END
) AS latest_date
FROM Event e
LEFT JOIN EventClinic ec ON e.id = ec.event_id
LEFT JOIN ClinicComment cc ON ec.clinic_id = cc.clinic_id
LEFT JOIN AttendersInfo af ON af.event_id = e.id
WHERE e.status = 'active'
AND (
e.id =43
OR e.id =45
)
GROUP BY e.title
ORDER BY latest_date DESC


Thanks

Answer

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

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

Or

 MAX(
    GREATEST(
        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

Comments