Re: Find min and max values across two columns?

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.

Thanks for your time! Any help or pointers to relevant reading
material on this would be greatly appreciated. (I have been using the
excellent PostgreSQL docs and an equally good book titled Beginning
Databases with PostgreSQL by Neil Matthew and Richard Stones so far.)

--
Amos

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;

--

Amos Hayes <ahayes(at)polkaroo(dot)net> writes:
> I'm trying to build a query that among other things, returns the
> minimum and maximum values contained in either of two columns.

I think you might be looking for

select greatest(max(columnA), max(columnB)) from tab;
select least(min(columnA), min(columnB)) from tab;

greatest/least are relatively new but you can roll your own in
older PG releases.

regards, tom lane

On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote:
> Rod Taylor <pg(at)rbt(dot)ca> writes:
> > 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;
>
> Just for the record, we've gotten that right since 7.4. greatest()
> would be a notationally cleaner solution than CASE, but multiple
> occurrences of identical aggregates don't cost much of anything.

Thanks. I could not remember one way or the other.

--

Rod Taylor <pg(at)rbt(dot)ca> writes:
> 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;

Just for the record, we've gotten that right since 7.4. greatest()
would be a notationally cleaner solution than CASE, but multiple
occurrences of identical aggregates don't cost much of anything.

regards, tom lane

Your tips were great and I have hunted down the relevant pages in the
docs. Thanks guys!

--
Amos

On 24-Mar-06, at 4:20 PM, Rod Taylor wrote:

> On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote:
>> Rod Taylor <pg(at)rbt(dot)ca> writes:
>>> 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;
>>
>> Just for the record, we've gotten that right since 7.4. greatest()
>> would be a notationally cleaner solution than CASE, but multiple
>> occurrences of identical aggregates don't cost much of anything.
>
> Thanks. I could not remember one way or the other.
>
> --
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly

On Fri, Mar 24, 2006 at 04:00:35PM -0500, Tom Lane wrote:
> Amos Hayes <ahayes(at)polkaroo(dot)net> writes:
> > I'm trying to build a query that among other things, returns the
> > minimum and maximum values contained in either of two columns.
>
> I think you might be looking for
>
> select greatest(max(columnA), max(columnB)) from tab;
> select least(min(columnA), min(columnB)) from tab;
>
> greatest/least are relatively new but you can roll your own in
> older PG releases.

And if you care about performance you might also try:

SELECT max(greatest(column_a, column_b) ...
SELECT min(least(column_a, column_b) ...

There may be a difference in performance between the two.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Hello,

I tried "select greatest(max(a), max(b)) from public.test", but I got
the following errors:

ERROR: function greatest(integer, integer) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.

May I know where I can read the docs about greatest & least please.

I am using PostgreSQL 8.0.7.

Thanks,
Ying

>Amos Hayes <ahayes(at)polkaroo(dot)net> writes:
>
>
>>I'm trying to build a query that among other things, returns the
>>minimum and maximum values contained in either of two columns.
>>
>>
>
>I think you might be looking for
>
> select greatest(max(columnA), max(columnB)) from tab;
> select least(min(columnA), min(columnB)) from tab;
>
>greatest/least are relatively new but you can roll your own in
>older PG releases.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

On Mon, 2006-05-15 at 16:40, Emi Lu wrote:
> Hello,
>
> I tried "select greatest(max(a), max(b)) from public.test", but I got
> the following errors:
>
> ERROR: function greatest(integer, integer) does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> May I know where I can read the docs about greatest & least please.
>
> I am using PostgreSQL 8.0.7.

You need to define your problem better. Assuming these data:

a b
12 24
24 12
13 18
25 10

which should I get for greatest(max(a), max(b)) should I get? Do you
just want 25,24??? In that case you don't need greatest. Do you need
the highest total between the two, or what?

Emi Lu <emilu(at)encs(dot)concordia(dot)ca> writes:
> ERROR: function greatest(integer, integer) does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.

We added greatest/least in 8.1, but before that you can just use a
CASE expression instead, along the lines of
case when x>y then x else y end

regards, tom lane

Thank you Tom.

>Emi Lu <emilu(at)encs(dot)concordia(dot)ca> writes:
>
>
>>ERROR: function greatest(integer, integer) does not exist
>>HINT: No function matches the given name and argument types. You may
>>need to add explicit type casts.
>>
>>
>
>We added greatest/least in 8.1, but before that you can just use a
>CASE expression instead, along the lines of
> case when x>y then x else y end
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>