How to find least non-null column in one particula

2020-02-23 07:56发布

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.

标签: sql mysql
7条回答
放我归山
2楼-- · 2020-02-23 08:23

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`
查看更多
一夜七次
3楼-- · 2020-02-23 08:25

SELECT MIN(LEAST(COALESCE(COL1, COL2), COALESCE(COL2,CO1))) WHERE COL1 IS NOT NULL AND COL2 IS NOT NULL;

查看更多
The star\"
4楼-- · 2020-02-23 08:32

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
查看更多
够拽才男人
5楼-- · 2020-02-23 08:38

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))
查看更多
Summer. ? 凉城
6楼-- · 2020-02-23 08:47

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
查看更多
Fickle 薄情
7楼-- · 2020-02-23 08:47

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.

e.g.

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
查看更多
登录 后发表回答