Re: find the greatest, pick it up and group by

Hi

The method you're using is functionally correct and quite efficient if
a little on the verbose side.

Other non-postgres variants of SQL have a "DECODE" function which
comes in very handy.
I dont believe postgres has any equivalent. (Postgres decode() does
something entirely differnt).

I often use nested queries in the from clause for this purpose.

SELECT a, b, c,
      x, y, z,
      case when gr = x then 'x' when gr = y then 'y' when gr = z then 'z' end
 FROM (
        Select distinct a,b,c,
               x,y,z,
               greatest(x,y,z) as gr
          from foo
)

Regards

On 17 May 2011 01:26, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:
> On Mon, 16 May 2011 20:05:45 -0400
> "David Johnston" <polobo(at)yahoo(dot)com> wrote:
>
>> When asking for help on non-trivial SELECT queries it really helps
>> to tell us the version of PG you are using so that responders know
>> what functionality you can and cannot use.  In this case
>> specifically, whether WINDOW (and maybe WITH) clauses available?
>
> Unfortunately I'm on 8.3 so no WINDOW.
>
> I didn't even think of using them and I can't think of any way to
> use WINDOW/WITH but if there is a more readable solution that use
> them I'd like to see it even if I won't be able to use it.
> Of course I'm more interested to know if there is any cleaner
> solution for 8.3.
>
> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>