From: Rod Taylor <pg@rbt.ca>
To: Amos Hayes <ahayes@polkaroo.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <DAD4674C-1896-4F24-B43B-B884491DB931@polkaroo.net>
References: <DAD4674C-1896-4F24-B43B-B884491DB931@polkaroo.net>
Date: Fri, 24 Mar 2006 15:46:55 -0500
Message-Id: <1143233215.90799.91.camel@home>
Mime-Version: 1.0
Subject: Re: Find min and max values across two columns?
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Fri, 2006-03-24 at 15:19 -0500, Amos Hayes wrote:
> Hello. I've recently begun to use PostgreSQL in earnest (working with  
> data as opposed to just having clever applications tuck it away in  
> there) and have hit a wall with something.
> 
> I'm trying to build a query that among other things, returns the  
> minimum and maximum values contained in either of two columns. The  
> problem is that min() and max() only take one column name as an  
> argument. Is there some clever way that I can craft an expression to  
> find the min/max across both columns? I have yet to delve into any  
> pgsql so if it requires that, then please go easy on me. :)
> 
> Example:
> 
> id | columnA | columnB
> 1 | 4 | 2
> 2 | 3 | 4
> 2 | 5 | 1
> 
> 
> I'd like to be able to discover that 1 is the smallest value in  
> either columnA or columnB and 5 is the largest. I don't actually care  
> what rows they are in, I just want the values.

rk=# create table tab (id integer, a integer, b integer);
CREATE TABLE
rk=# insert into tab values (1, 4, 2);
INSERT 0 1
rk=# insert into tab values (2,3,4);
INSERT 0 1
rk=# insert into tab values (2,5,1);
INSERT 0 1
rk=# select case when maxa > maxb then maxa else maxb end as max
	, case when mina < minb then mina else minb end as min
	 from (select max(a) as maxa
			, max(b) as maxb
			, min(a) as mina
			, min(b) as minb
		 from tab
	) as tabalias;
 max | min
-----+-----
   5 |   1
(1 row)


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;

--