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:
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.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
- 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 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
Why not set the value of one column to be equal to the other column when it’s NULL?
SELECT LEAST(IFNULL(COL1, COL2), IFNULL(COL2, COL1));
with the code above, the null value will be ignored unless both are null.
COL1 = NULL, COL2 = 5
LEAST(IFNULL(NULL, 5), IFNULL(5, NULL)) -> LEAST(5, 5) -> 5
COL1 = 3, COL2 = NULL
LEAST(IFNULL(3, NULL), IFNULL(NULL, 3)) -> LEAST(3, 3) -> 3
COL1 = NULL, COL2 = NULL
LEAST(IFNULL(NULL, NULL), IFNULL(NULL, NULL)) -> LEAST(NULL, NULL) -> NULL
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.
MIN(LEAST(COALESCE(COL1, COL2), COALESCE(COL2,CO1)))
WHERE COL1 IS NOT NULL
AND COL2 IS NOT NULL;
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:
- delimiter separated string of dates as TEXT
- delimiter as TEXT (same as used on CONCAT_WS !!) – you can remove it if you use only preferred separator on CONCAT_WS.
CREATE FUNCTION `min_date`(`dates` TEXT, `delim` VARCHAR(10)) RETURNS DATE NO SQL DETERMINISTIC BEGIN DECLARE `result` DATE DEFAULT NULL; DECLARE `count` TINYINT DEFAULT 0; DECLARE `temp` DATE DEFAULT NULL; IF `delim` IS NULL THEN SET `delim` = ','; END IF; IF `dates` IS NOT NULL AND CHAR_LENGTH(`dates`) > 0 THEN SET `count` = LENGTH(`dates`) - LENGTH(REPLACE(`dates`, `delim`, SPACE(CHAR_LENGTH(`delim`) - 1))); WHILE `count` >= 0 DO SET `temp` = SUBSTRING_INDEX(SUBSTRING_INDEX(`dates`, `delim`, `count` + 1), `delim`, -1); IF `result` IS NULL OR `result` > `temp` THEN SET `result` = `temp`; END IF; SET `count` = `count` - 1; END WHILE; END IF; RETURN `result`; END
Then, you can use in any combination of date fields or as static strings (as long as are valid dates or NULL):
SELECT min_date(CONCAT_WS(',', `date_column_1`, NULL, '2019-03-04', `date_column_2`), ',') AS `min_date`
One simple (yet not beautiful) solution is the following.
If you’re looking for the smallest non-null value, you can use IFNULL with the second parameter beingthe ‘INT limit’
ORDER BY LEAST( IFNULL(properties.sale_value, 2147483647), IFNULL(properties.rental_value, 2147483647), IFNULL(properties.daily_rental_value, 2147483647) ) ASC
And if you’re looking for the biggest non-null value, you can use IFNULL with the second parameter being 1, ( or the first negative value below your limit, if you don’t know it, use the negative int limit )
ORDER BY GREATEST( IFNULL(properties.sale_value, 1), IFNULL(properties.rental_value, 1), IFNULL(properties.daily_rental_value, 1) ) ASC