Re: Find min and max values across two columns? - Mailing list pgsql-sql

From Rod Taylor
Subject Re: Find min and max values across two columns?
Date
Msg-id 1143233215.90799.91.camel@home
Whole thread Raw
In response to Find min and max values across two columns?  (Amos Hayes <ahayes@polkaroo.net>)
Responses Re: Find min and max values across two columns?
List pgsql-sql
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;

-- 



pgsql-sql by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: generate_series to return row that doesn't exist in
Next
From: MaXX
Date:
Subject: Re: generate_series to return row that doesn't exist in