From: | Rod Taylor <pg(at)rbt(dot)ca> |
---|---|
To: | Amos Hayes <ahayes(at)polkaroo(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Find min and max values across two columns? |
Date: | 2006-03-24 20:46:55 |
Message-ID: | 1143233215.90799.91.camel@home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
--
From | Date | Subject | |
---|---|---|---|
Next Message | MaXX | 2006-03-24 20:54:28 | Re: generate_series to return row that doesn't exist in |
Previous Message | Scott Marlowe | 2006-03-24 20:42:33 | Re: generate_series to return row that doesn't exist in |