How to get at least 3 values ​​where some of them may be empty in MySQL using the LEAST () method?

Question

I found this question ( How to get a maximum of two values ​​in MySQL? ), Looking for ways to compare a discrete set of values.

I want to get a minimum of several selected values, where some of them may be null , because they are optional, but MySQL docs says:

If any argument is NULL, the result will be NULL.

Answer 1

Use the COALESCE() function for values ​​with a null value:

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

if you get the maximum value of int, then both values ​​were zero (if you do not have a sufficient chance of the actual value of max int as a real argument, then more logic is required)

Answer 2

How about this:

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

Obviously, this does not scale to more than 3 values.

Answer 3

It works, easily extends and does not depend on any values ​​that are not found in the data, but are probably extremely inefficient!

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

Request

 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 

Answer 4

You need to write the code for a large number 99999.

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

i.e. just add IFNULL and then the value or column name with a sufficiently large number.