Somehow I have used SQL for nearly 30 years without knowing about the GREATEST function. It’s only when you need to do something that looks impossible that you delve into the online manual and find gems like this.
In this case, I had a query that needed to return, and order the rows by, the latest date from three different date columns in three different tables. Impossible I thought — I’ll have to muck about with MAX for each column, and then do the comparison in PHP. Not at all!
SELECT R.requestid, C.cliid, clifname, clilname,requestdate, GREATEST(emaildate,climoddate,requestlastmodified) AS lastmoddate
FROM ClientRequests R
INNER JOIN Clients C ON C.cliid = R.cliid
LEFT OUTER JOIN Emails E ON R.requestid = E.requestid
GROUP BY R.requestid
ORDER BY GREATEST(emaildate,climoddate,requestlastmodified)
If one or more of the columns might include null values, you need to do a little more work with COALESCE:
SELECT R.requestid, C.cliid, clifname, clilname,requestdate, GREATEST(COALESCE(emaildate,0),climoddate,requestlastmodified) AS lastmoddate
FROM ClientRequests R
INNER JOIN Clients C ON C.cliid = R.cliid
LEFT OUTER JOIN Emails E ON R.requestid = E.requestid
GROUP BY R.requestid
ORDER BY GREATEST(COALESCE(emaildate,0),climoddate,requestlastmodified)
