How to find least non-null column in one particular row in SQL?

Question

I am trying to find the lowest number in two columns of a row in the same table, with the caveat that one of the columns may be null in a particular row. If one of the columns is null, I want the value in the other column returned for that row, as that is the lowest non-null column in this case. If I use the least() function in MySQL 5.1:

select least(1,null)

This returns null, which is not what I want. I need the query to return 1 in this case.

I've been able to get the result I want in general with this query:

select least(coalesce(col1, col2)) , coalesce(col2,col1))

As long as col1 and col2 are both not null each coalesce statement will return a number, and the least() handles finding the lowest.

Is there a simpler/faster way to do this? I'm using MySQL in this instance but general solutions are welcomed.

Answer 1

This may perform a bit better (may have to be converted to corresponding MySql syntax):

SELECT
  CASE
    WHEN Col1 IS NULL THEN Col2
    WHEN Col2 IS NULL THEN Col1
    ELSE Least(Col1, Col2)
  END

Another alternative (probably slower though, but worth a try):

SELECT Col1
WHERE Col2 IS NULL
UNION
SELECT Col2
WHERE Col1 IS NULL
UNION
SELECT least(Col1, Col2)
WHERE Col1 IS NOT NULL AND Col2 IS NOT NULL

Answer 2

Unfortunately (for your case) behaviour of LEAST was changed in MySQL 5.0.13 (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_least) - it used to return NULL only if all arguments are NULL.

This change was even reported as a bug: http://bugs.mysql.com/bug.php?id=15610 But the fix was only to MySQL documentation, explaining new behaviour and compatibility break.

Your solution was one of the recommended workarounds. Another can be using IF operator:

SELECT IF(Col1 IS NULL OR Col2 IS NULL, COALESCE(Col1, Col2), LEAST(Col1,Col2))