X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BBAD79DC8A3 for ; Fri, 24 Mar 2006 17:20:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51181-09 for ; Fri, 24 Mar 2006 17:20:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 6EB3A9DC813 for ; Fri, 24 Mar 2006 17:20:29 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k2OLKVpU010496; Fri, 24 Mar 2006 16:20:31 -0500 (EST) To: Rod Taylor cc: Amos Hayes , pgsql-sql@postgresql.org Subject: Re: Find min and max values across two columns? In-reply-to: <1143233215.90799.91.camel@home> References: <1143233215.90799.91.camel@home> Comments: In-reply-to Rod Taylor message dated "Fri, 24 Mar 2006 15:46:55 -0500" Date: Fri, 24 Mar 2006 16:20:31 -0500 Message-ID: <10495.1143235231@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/395 X-Sequence-Number: 24741 Rod Taylor writes: > The reason for the subselect is to prevent multiple calculations of > individual column aggregates. I believe it *may* be calculated multiple > times otherwise this would work just as well: > select case when max(a) > max(b) then max(a) else max(b) end as max from > tab; Just for the record, we've gotten that right since 7.4. greatest() would be a notationally cleaner solution than CASE, but multiple occurrences of identical aggregates don't cost much of anything. regards, tom lane