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

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

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

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

This is how I solved it:

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

If one value is NULL, the query will return the first non-NULL value. You can even add a default value as the last parameter, if both values can be NULL.

Tags:

Mysql

Sql

Recent Posts