From: Rod Taylor <pg@rbt.ca>
To: Amos Hayes <ahayes@polkaroo.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Find min and max values across two columns?
Date: Fri, 24 Mar 2006 15:46:55 -0500
Message-ID: <1143233215.90799.91.camel@home> (raw)
In-Reply-To: <DAD4674C-1896-4F24-B43B-B884491DB931@polkaroo.net>
References: <DAD4674C-1896-4F24-B43B-B884491DB931@polkaroo.net>
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;
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pg@rbt.ca, ahayes@polkaroo.net
Subject: Re: Find min and max values across two columns?
In-Reply-To: <1143233215.90799.91.camel@home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox