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

Question

I have a query that looks something like this:

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

Valid values ​​for timeMonday are: null, -1, 0, 1-24. At the moment, MAX () puts the preference of these values ​​in null, -1, 0, 1-24 order, however what I really want is -1, null, 0, 1-24, so the null value is considered higher than -1. I know that MAX cannot do this, and what is the easiest way to achieve this?

Answer 1

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

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

NULL -0.5 MAX, NULL.

( MS SQL Server)

Answer 3

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

Edit: did not check this

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