Re: greatest/least semantics different between oracle and postgres

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> "Bruno Wolff III" <bruno(at)wolff(dot)to> writes:
>>> Also what value should I have used in a coalesce to guaranty still getting
>>> the maximum?
>
>> I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's
>> not terribly legible though and if a and b are subselects I would worry a
>> little about the optimizer rerunning them unnecessarily.
>
> That does not work correctly for volatile functions, and it does not
> scale to more than two inputs either -- you'd get the first nonnull
> not the largest one.

Both true.

There is another option too if you have a minimum value below which you know
no values will exist:

SELECT nullif(greatest(coalesce(a,-1), coalesce(b,-1), coalesce(c,-1)), -1)

Does Oracle even have nullif() these days? If not you would have to use
decode() but I think it suffers from the same problem of repeated evaluation.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com