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; -- ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq