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 200419DC863 for ; Mon, 27 Mar 2006 09:56:29 -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 64304-06 for ; Mon, 27 Mar 2006 09:56:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 0D28A9DC800 for ; Mon, 27 Mar 2006 09:56:26 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 67BCD56427; Mon, 27 Mar 2006 07:56:31 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Mon, 27 Mar 2006 07:56:29 -0600 Date: Mon, 27 Mar 2006 07:56:29 -0600 From: "Jim C. Nasby" To: Tom Lane Cc: Amos Hayes , pgsql-sql@postgresql.org Subject: Re: Find min and max values across two columns? Message-ID: <20060327135629.GM80726@pervasive.com> References: <9558.1143234035@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9558.1143234035@sss.pgh.pa.us> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060327:tgl@sss.pgh.pa.us::HM9xRfGqQqSCgTT2:00000000000000000000 0000000000000000000000001r2S X-Hashcash: 1:20:060327:ahayes@polkaroo.net::HwyCMNnvxol3U1Kx:000000000000000000 000000000000000000000000AxWt X-Hashcash: 1:20:060327:pgsql-sql@postgresql.org::k9kPXgEahavWQkgx:0000000000000 000000000000000000000000EHPL X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109] X-Spam-Score: 0.109 X-Spam-Level: X-Archive-Number: 200603/418 X-Sequence-Number: 24764 On Fri, Mar 24, 2006 at 04:00:35PM -0500, Tom Lane wrote: > Amos Hayes writes: > > I'm trying to build a query that among other things, returns the > > minimum and maximum values contained in either of two columns. > > I think you might be looking for > > select greatest(max(columnA), max(columnB)) from tab; > select least(min(columnA), min(columnB)) from tab; > > greatest/least are relatively new but you can roll your own in > older PG releases. And if you care about performance you might also try: SELECT max(greatest(column_a, column_b) ... SELECT min(least(column_a, column_b) ... There may be a difference in performance between the two. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461