pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFind min and max values across two columns?
11+ messages / 6 participants
[nested] [flat]
* Find min and max values across two columns?
@ 2006-03-24 20:19 Amos Hayes <ahayes@polkaroo.net>
2006-03-24 20:46 ` Re: Find min and max values across two columns? Rod Taylor <pg@rbt.ca>
2006-03-24 21:00 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Amos Hayes @ 2006-03-24 20:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
@ 2006-03-24 20:46 ` Rod Taylor <pg@rbt.ca>
2006-03-24 21:20 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Rod Taylor @ 2006-03-24 20:46 UTC (permalink / raw)
To: Amos Hayes <ahayes@polkaroo.net>; +Cc: 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;
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 20:46 ` Re: Find min and max values across two columns? Rod Taylor <pg@rbt.ca>
@ 2006-03-24 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-24 21:20 ` Re: Find min and max values across two columns? Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2006-03-24 21:20 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Amos Hayes <ahayes@polkaroo.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 20:46 ` Re: Find min and max values across two columns? Rod Taylor <pg@rbt.ca>
2006-03-24 21:20 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-24 21:20 ` Rod Taylor <pg@rbt.ca>
2006-03-24 21:34 ` Re: Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
0 siblings, 1 reply; 11+ messages in thread
From: Rod Taylor @ 2006-03-24 21:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Amos Hayes <ahayes@polkaroo.net>; pgsql-sql
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.
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 20:46 ` Re: Find min and max values across two columns? Rod Taylor <pg@rbt.ca>
2006-03-24 21:20 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
2006-03-24 21:20 ` Re: Find min and max values across two columns? Rod Taylor <pg@rbt.ca>
@ 2006-03-24 21:34 ` Amos Hayes <ahayes@polkaroo.net>
0 siblings, 0 replies; 11+ messages in thread
From: Amos Hayes @ 2006-03-24 21:34 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
@ 2006-03-24 21:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 13:56 ` Re: Find min and max values across two columns? Jim C. Nasby <jnasby@pervasive.com>
2006-05-15 21:40 ` Re: Find min and max values across two columns? Emi Lu <emilu@encs.concordia.ca>
1 sibling, 2 replies; 11+ messages in thread
From: Tom Lane @ 2006-03-24 21:00 UTC (permalink / raw)
To: Amos Hayes <ahayes@polkaroo.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 21:00 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-27 13:56 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jim C. Nasby @ 2006-03-27 13:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Amos Hayes <ahayes@polkaroo.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 21:00 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-15 21:40 ` Emi Lu <emilu@encs.concordia.ca>
2006-05-15 21:50 ` Re: Find min and max values across two columns? Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-16 00:08 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 11+ messages in thread
From: Emi Lu @ 2006-05-15 21:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Amos Hayes <ahayes@polkaroo.net>; pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 21:00 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
2006-05-15 21:40 ` Re: Find min and max values across two columns? Emi Lu <emilu@encs.concordia.ca>
@ 2006-05-15 21:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2006-05-15 21:50 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Amos Hayes <ahayes@polkaroo.net>; pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 21:00 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
2006-05-15 21:40 ` Re: Find min and max values across two columns? Emi Lu <emilu@encs.concordia.ca>
@ 2006-05-16 00:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-16 14:44 ` Re: Find min and max values across two columns? Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2006-05-16 00:08 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Amos Hayes <ahayes@polkaroo.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Find min and max values across two columns?
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 21:00 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
2006-05-15 21:40 ` Re: Find min and max values across two columns? Emi Lu <emilu@encs.concordia.ca>
2006-05-16 00:08 ` Re: Find min and max values across two columns? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-16 14:44 ` Emi Lu <emilu@encs.concordia.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Emi Lu @ 2006-05-16 14:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-05-16 14:44 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-24 20:19 Find min and max values across two columns? Amos Hayes <ahayes@polkaroo.net>
2006-03-24 20:46 ` Rod Taylor <pg@rbt.ca>
2006-03-24 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-24 21:20 ` Rod Taylor <pg@rbt.ca>
2006-03-24 21:34 ` Amos Hayes <ahayes@polkaroo.net>
2006-03-24 21:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 13:56 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-15 21:40 ` Emi Lu <emilu@encs.concordia.ca>
2006-05-15 21:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-16 00:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-16 14:44 ` Emi Lu <emilu@encs.concordia.ca>
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