0

I would like to get the greatest value from a set of fields but some of them can be NULL so I can't use GREATEST as it is and I have to transform NULL values to arbitrary values (-1 in this case). However in the result I still have to get NULL value if all the fields were NULL.

SELECT 
CASE WHEN GREATEST(IFNULL(f1, -1), IFNULL(f2, -1)) = -1 THEN 
   NULL 
ELSE 
   GREATEST(IFNULL(f1, -1), IFNULL(f2, -1)) 
END

My problem is, I have twice the calculation of the greatest value so I wondered if there is a way to "store" the value so that mySQL doesn't have to evaluate twice.

Or if there is a better/ more proper way to do what i want.

Thanks.

davek
  • 22,499
  • 9
  • 75
  • 95
GdC
  • 189
  • 1
  • 1
  • 9
  • 1
    I think MySQL can tell that you're calculating the same thing twice, and it stores it internally. – Barmar Oct 30 '14 at 08:44
  • @Barmar: I would be highly surprised if it was the case for mysql, especially keeping in mind there is no something similar to `DETERMENISTIC` in oracle – zerkms Oct 30 '14 at 08:47
  • @zerkms See http://stackoverflow.com/questions/23824505/does-mysql-eliminate-common-subexpressions-between-select-and-having-group-by-cl – Barmar Oct 30 '14 at 08:48
  • @Barmar: I don't think these cases are similar. If mysql cannot tell a function is `DETERMENISTIC` - then it would be too risky to cache its value used in the same expression – zerkms Oct 30 '14 at 08:58
  • @Barmar PS: `select case sleep(1) when 1 then 2 else sleep(1) end` executes in 2s on sqlfiddle – zerkms Oct 30 '14 at 09:01
  • @GdC: it makes no sense to memoize trivial math. – zerkms Oct 30 '14 at 09:09

1 Answers1

1

You may use NULLIF function:

NULLIF(GREATEST(IFNULL(f1, -1), IFNULL(f2, -1)), -1)

It would return NULL if they equal, or the value of the first argument otherwise.

References:

zerkms
  • 249,484
  • 69
  • 436
  • 539