Обсуждение: Get the max(value1, value2, value3) from a table
Greetings, Version: PostgreSQL 8.0.13 on i686-pc-linux-gnu I have a table test(col1, col2, col3) For each row, I'd like to get the "max"(col1, col2, col3). For example, test(1, 5, 2) test(8, 1, 3) test(12, 1, 1) select ?max?(col1, col2, col3) as result; will return result ------- 5 8 12 (3 rows) Thanks! Ly.
On Jan 7, 2008 4:03 PM, Emi Lu <emilu@encs.concordia.ca> wrote: > Greetings, > > Version: PostgreSQL 8.0.13 on i686-pc-linux-gnu > > I have a table test(col1, col2, col3) > > For each row, I'd like to get the "max"(col1, col2, col3). > > For example, test(1, 5, 2) > test(8, 1, 3) > test(12, 1, 1) > > > select ?max?(col1, col2, col3) as result; > will return > > result > ------- > 5 > 8 > 12 select max(col1) from table union all select max(col2) from table union all select max(col3) from table
--- On Mon, 1/7/08, Scott Marlowe <scott.marlowe@gmail.com> wrote: > select max(col1) from table > union all > select max(col2) from table > union all > select max(col3) from table Would the following work also? SELECT MAX( GREATEST( col1, col2, col3 ) ) FROM TABLE; Regards, Richard Broersma Jr.
On Jan 7, 2008 4:27 PM, Richard Broersma Jr <rabroersma@yahoo.com> wrote: > --- On Mon, 1/7/08, Scott Marlowe <scott.marlowe@gmail.com> wrote: > > > select max(col1) from table > > union all > > select max(col2) from table > > union all > > select max(col3) from table > > Would the following work also? > > SELECT MAX( GREATEST( col1, col2, col3 ) ) > FROM TABLE; Not given his example output. Given that output, he wants the max of each column, and your example would only return a single value.
> >> select max(col1) from table >> union all >> select max(col2) from table >> union all >> select max(col3) from table No, this is not what I prefer; it makes complicate query. > > Would the following work also? > > SELECT MAX( GREATEST( col1, col2, col3 ) ) > FROM TABLE; I would prefer this func. Unfortunately, the current version I have 8.02(http://www.postgresql.org/docs/8.0/static/functions-conditional.html) does not support this func I am afraid :( Thanks ! Ly.
On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote: > select ?max?(col1, col2, col3) as result; > will return > > result > ------- > 5 > 8 > 12 > > (3 rows) 8.1 (I believe?) introduced GREATEST(), which does precisely what you're looking for. But if 8.0 is a must, you'll probably have to create your own function to do that. Which should be fairly easy to do if you're working with a static number of columns/data types/etc... - Josh
On Jan 7, 2008 4:37 PM, Emi Lu <emilu@encs.concordia.ca> wrote: > > > > >> select max(col1) from table > >> union all > >> select max(col2) from table > >> union all > >> select max(col3) from table > No, this is not what I prefer; it makes complicate query. Generally speaking when you have to make complicated queries to get simple answers, then it's likely you have a normalization issue. IF col1, col2, col3 were all a single column in an external table the answer would likely fall out a little simpler. But honestly, that's not a real complex query. PostgreSQL handles far more complex queries for me everyday with hardly a murmer..
On Jan 7, 2008 4:38 PM, Josh Williams <joshwilliams@ij.net> wrote: > On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote: > > select ?max?(col1, col2, col3) as result; > > will return > > > > result > > ------- > > 5 > > 8 > > 12 > > > > (3 rows) > > 8.1 (I believe?) introduced GREATEST(), which does precisely what you're > looking for. How would greatest give him three rows like that? Maybe I'm misunderstanding what the OP was asking for...
>>> select ?max?(col1, col2, col3) as result; >>> will return >>> >>> result >>> ------- >>> 5 >>> 8 >>> 12 >>> >>> (3 rows) >> 8.1 (I believe?) introduced GREATEST(), which does precisely what you're >> looking for. > > How would greatest give him three rows like that? Maybe I'm > misunderstanding what the OP was asking for... IF 8.1, "select greatest(col1, col2, col3) from test" is exactly what I am looking for. I would do the optional query by union/or for now. Thanks! Ly.
On Jan 7, 2008 4:53 PM, Emi Lu <emilu@encs.concordia.ca> wrote: > >>> select ?max?(col1, col2, col3) as result; > >>> will return > >>> > >>> result > >>> ------- > >>> 5 > >>> 8 > >>> 12 > >>> > >>> (3 rows) > >> 8.1 (I believe?) introduced GREATEST(), which does precisely what you're > >> looking for. > > > > How would greatest give him three rows like that? Maybe I'm > > misunderstanding what the OP was asking for... > > IF 8.1, "select greatest(col1, col2, col3) from test" is exactly what I > am looking for. > > I would do the optional query by union/or for now. OK, looking back at your example, I do think I got it wrong. The greatest thing should work... Here's a test from 8.1 to prove it ... create table test (col1 int, col2 int, col3 int); insert into test values (1,5,2); smarlowe=# insert into test values (8,1,3); smarlowe=# insert into test values (12,1,1); select greatest(col1,col2,col3) from test;greatest ---------- 5 8 12 tada! So yeah, you want 8.1 (or 8.2 or 8.3)
On Jan 7, 2008, at 4:43 PM, Scott Marlowe wrote: > On Jan 7, 2008 4:38 PM, Josh Williams <joshwilliams@ij.net> wrote: >> On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote: >>> select ?max?(col1, col2, col3) as result; >>> will return >>> >>> result >>> ------- >>> 5 >>> 8 >>> 12 >>> >>> (3 rows) >> >> 8.1 (I believe?) introduced GREATEST(), which does precisely what >> you're >> looking for. > > How would greatest give him three rows like that? Maybe I'm > misunderstanding what the OP was asking for... His test data was: col1 col2 col3 ---------------------- 1 5 2 8 1 3 12 1 1 So, SELECT test(col1, col2, col3); returning test ----- 5 8 12 Is giving the max of the three columns for each row. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com