How to find least non-null column in one particula

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