Re: Find min and max values across two columns?

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;

--