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

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))

Answer 2

Depending on your corner case situation of having all values be null, I would go for such syntax, which is more readable (An easier solution if you have exactly two columns is below!)

SELECT LEAST( IFNULL(5, ~0 >> 1), IFNULL(10, ~0 >> 1) ) AS least_date;
-- Returns: 5

SELECT LEAST( IFNULL(null, ~0 >> 1), IFNULL(10, ~0 >> 1) ) AS least_date;
-- Returns: 10

SELECT LEAST( IFNULL(5, ~0 >> 1), IFNULL(null, ~0 >> 1) ) AS least_date;
-- Returns: 5

SELECT LEAST( IFNULL(null, ~0 >> 1), IFNULL(null, ~0 >> 1)) AS least_date
-- Returns: @MAX_VALUE (If you need to use it as default value)

SET @MAX_VALUE=~0 >> 1;
SELECT LEAST( IFNULL(null, @MAX_VALUE), IFNULL(null, @MAX_VALUE)) AS least_date;
-- Returns: @MAX_VALUE (If you need to use it as default value). Variables just makes it more readable!

SET @MAX_VALUE=~0 >> 1;
SELECT NULLIF(
    LEAST( IFNULL(null, @MAX_VALUE), IFNULL(null,@MAX_VALUE)),
    @MAX_VALUE
) AS least_date;
-- Returns: NULL

That is my prefered way if

• you can ensure that at least one column cannot be NULL
• in corner case situation (all columns are NULL) you want a non-null default value which greater than any possible value or can get limited to a certain threshold
• You can deal with variables to make this statement even more readable

If you question yourself what ~0 >> 1 means: It's just a short hand for saying "Give me the greatest number available". See also: https://stackoverflow.com/a/2679152/2427579

Even better, if you have only two columns, you can use:

SELECT LEAST( IFNULL(@column1, @column2), IFNULL(@column2, @column1) ) AS least_date;
-- Returns: NULL (if both columns are null) or the least value