MySQL MAX() and NULLs

Question

Hi, 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?

Thanks, Jack

Answer 1

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)

Answer 2

SELECT weekEnd, MAX(COALESCE(timeMonday, '-0.1'))
FROM timesheet
GROUP BY weekEnd

Edit: haven't tested this

SELECT weekEnd, REPLACE(MAX(COALESCE(timeMonday, '-0.1')), '-0.1', 'null')
FROM timesheet
GROUP BY weekEnd

Answer 3

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

Then convert -.5 back to null