PostgreSQL SQL - Find min and max values across two columns?

Author

Find min and max values across two columns?

Amos Hayes

2006-03-24, 8:26 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Rod Taylor

2006-03-24, 8:26 pm

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;

--

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2006-03-24, 8:26 pm

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

Tom Lane

2006-03-24, 8:26 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Rod Taylor

2006-03-24, 8:26 pm

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

Amos Hayes

2006-03-24, 8:26 pm

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:
>
> 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

---------------------------(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

Jim C. Nasby

2006-03-27, 9:38 am

On Fri, Mar 24, 2006 at 04:00:35PM -0500, Tom Lane wrote:
> Amos Hayes <ahayes@polkaroo.net> writes:
>
> 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

---------------------------(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

Sponsored Links