Thread: Find min and max values across two columns?

Find min and max values across two columns?

From
Amos Hayes
Date:
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


Re: Find min and max values across two columns?

From
Rod Taylor
Date:
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;

-- 



Re: Find min and max values across two columns?

From
Tom Lane
Date:
Amos Hayes <ahayes@polkaroo.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


Re: Find min and max values across two columns?

From
Tom Lane
Date:
Rod Taylor <pg@rbt.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


Re: Find min and max values across two columns?

From
Rod Taylor
Date:
On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote:
> Rod Taylor <pg@rbt.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.

-- 



Re: Find min and max values across two columns?

From
Amos Hayes
Date:
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@rbt.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@postgresql.org so that  
> your
>        message can get through to the mailing list cleanly



Re: Find min and max values across two columns?

From
"Jim C. Nasby"
Date:
On Fri, Mar 24, 2006 at 04:00:35PM -0500, Tom Lane wrote:
> Amos Hayes <ahayes@polkaroo.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Find min and max values across two columns?

From
Emi Lu
Date:
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@polkaroo.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
>  
>



Re: Find min and max values across two columns?

From
Scott Marlowe
Date:
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?


Re: Find min and max values across two columns?

From
Tom Lane
Date:
Emi Lu <emilu@encs.concordia.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 ofcase when x>y then x else y end
        regards, tom lane


Re: Find min and max values across two columns?

From
Emi Lu
Date:
Thank you Tom.

>Emi Lu <emilu@encs.concordia.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
>  
>