Re: Get the max(value1, value2, value3) from a table

On Jan 7, 2008 4:53 PM, Emi Lu <emilu(at)encs(dot)concordia(dot)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)