0

I need to sort a table by the difference of two columns as

SELECT (first - second) AS dif
    FROM table1
ORDER BY dif DESC

but the two columns can be NULL. In this case, it returns the error:

BIGINT UNSIGNED value is out of range

The solution to this error is to use IFNULL(col,0), but the problem is that I need dif to be NULL if any of the columns is NULL

  • In this case, it returns the error Extremely strange. I have tested it on MySQL 5.5.60, 5.6.10, 5.6.39, 5.6.40, 5.7.17, 5.7.22, 8.0.11 - all of them give the result you need. – Akina Aug 1 '18 at 19:42
  • @Akina you're absolutely right. The problem was not null values, but my unsigned column. Thanks for pointing me in the right direction. – Googlebot Aug 1 '18 at 23:26
2

You could test for the columns being null before doing the calculation.

SELECT if(isnull(first) or isnull(second),null,(ifnull(first,0) - ifnull(secon,0))) AS dif
    FROM table1
ORDER BY dif DESC
5

Many ways of doing this, but I find this readable:

SELECT CASE WHEN first IS NULL OR second IS NULL THEN NULL
       ELSE first - second END AS dif
    FROM table1
ORDER BY 1 DESC
0

If the datatype of the columns is BIGINT UNSIGNED, then consider doing CAST(x AS SIGNED)

See also the 'operator' <=>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.