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

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.

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)

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.

Works, is easily extendible, and doesn't rely on any values not being found in the data but probably heinously inefficient!

CREATE TABLE X
(
Id int primary key,
col1 int null,
col2 int null,
col3 int null
)

Query

SELECT id,
       Min(CASE c
             WHEN 1 THEN col1
             WHEN 2 THEN col2
             WHEN 3 THEN col3
           END)
FROM   x,
       (SELECT 1 AS c
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3) t
GROUP  BY id  

You have to hard code a big number say 99999.

LEAST( IFNULL(COL_1,999999) ,
IFNULL(COL_2,999999) , IFNULL(COL_3,999999) , IFNULL(COL_1,999999) )

i.e. just add IFNULL then the value or column name with a big enough integer.