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.

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

>
>> 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/s...nditional.html)
does not support this func I am afraid

Thanks !
Ly.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate