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.
I've created a function which handles any number of dates, by concatenating them with a separator (CONCAT_WS) as first parameter to the function.
CONCAT_WS
besides dynamic number of parameters, will remove all NULL dates ;)The function accepts two parameters:
Then, you can use in any combination of date fields or as static strings (as long as are valid dates or NULL):
SELECT MIN(LEAST(COALESCE(COL1, COL2), COALESCE(COL2,CO1))) WHERE COL1 IS NOT NULL AND COL2 IS NOT NULL;
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!)That is my prefered way if
NULL
NULL
) you want a non-null default value which greater than any possible value or can get limited to a certain thresholdIf 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/2427579Even better, if you have only two columns, you can use:
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:
This may perform a bit better (may have to be converted to corresponding MySql syntax):
Another alternative (probably slower though, but worth a try):
Why not set the value of one column to be equal to the other column when it's NULL?
with the code above, the null value will be ignored unless both are null.
e.g.
COL1 = NULL, COL2 = 5
COL1 = 3, COL2 = NULL
COL1 = NULL, COL2 = NULL