"Tom Lane" <tgl@xxxxxxxxxxxxx> writes: > Gregory Stark <stark@xxxxxxxxxxxxxxxx> writes: >> "Bruno Wolff III" <bruno@xxxxxxxx> 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
