"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> "Bruno Wolff III" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
