How to get the minimum of 3 values where some of them may be null in MySQL using LEAST()?

Question

I found this question ( How to get the max of two values in MySQL? ) while looking for ways to compare discrete set of values.

I want to be able to get the minimum of few selected values where some of them may be null because they are optional but the MySQL docs says:

If any argument is NULL, the result is NULL.

Answer 1

Use a COALESCE() function on the nullable values:

select LEAST(COALESCE(@foo, <max int>), COALESCE(@bar, <max int>));

if you get the max int value, then both were null (unless there's a decent chance you could actually have the max int value as a real argument, then more logic is necessary)

Answer 2

How about this:

LEAST(COALESCE(col1, col2, col3),
      COALESCE(col2, col3, col1),
      COALESCE(col3, col1, col2))

Obviously this doesn't scale well to more than 3 values.