Обсуждение: 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@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
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
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. --
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
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
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 > >
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@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
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 > >