How to make MySQL MAX() consider NULL the smallest possible value?

Question

I have a query that looks a bit like this:

SELECT weekEnd, MAX(timeMonday)
FROM timesheet
GROUP BY weekEnd

The valid values for timeMonday are: null, -1, 0, 1-24. At the moment, MAX() places the preference of those values in the order null, -1, 0, 1-24, however what I actually want is -1, null, 0, 1-24, so that null is considered higher than -1. I know MAX cant do this, so what's the easiest way to achieve it?

Answer 1

SELECT weekEnd, MAX(coalesce(timeMonday,-.5)) FROM timesheet GROUP BY weekEnd

Then convert -.5 back to null

Answer 2

SELECT weekEnd, CASE WHEN maxTimeMonday = -0.5 THEN NULL ELSE maxTimeMonday END maxTimeMonday 
FROM (
   SELECT weekEnd, MAX(CASE WHEN timeMonday IS NULL THEN -0.5 ELSE timeMonday END) maxTimeMonday 
     FROM timesheet
    GROUP BY weekEnd) T

replacing NULL by -0.5 when calculating MAX then reverting it to NULL.

(MS SQL Server syntax)