Обсуждение: find the greatest, pick it up and group by

I've a table like:

CREATE TABLE ordiniitem
(
  idordine numeric(18,0) NOT NULL,
  grupposped smallint,
  idart numeric(18,0) NOT NULL,
  qevasa integer,
  qfuoricat integer,
  qinris integer,
  qnonpub integer,
  qann integer,
  qord integer,
  qpren integer,
  qrichpag integer,
  qinriass integer,
  qinesa integer
);

I'd like to group by idordine, grupposped, idart.
For every row grouped that way, I'd like to pick the greatest of the
q* columns and insert:
idordine, grupposped, idart, name of the greatest(q*) in a new table.
I don't mind if more than one q* column is equal to greatest(q*). It
should pick up one, just one no matter which among the one equal to
greatest(q*).

I think this way works but it hurts my eyes. Any alternative
approach?

SELECT
  ioi.idordine,
  ioi.grupposped,
  ioi.idart,
  -- ioi.quantita,
  case
   when ioi.qevasa =
    greatest(
      ioi.qevasa, ioi.qfuoricat,
      ioi.qinris, ioi.qnonpub, ioi.qann, ioi.qord, ioi.qpren,
      ioi.qrichpag, ioi.qinriass, ioi.qinesa )
    then 'evaso'
   when ioi.qfuoricat =
    greatest(
      ioi.qevasa, ioi.qfuoricat,
      ioi.qinris, ioi.qnonpub, ioi.qann, ioi.qord, ioi.qpren,
      ioi.qrichpag, ioi.qinriass, ioi.qinesa )
    then 'fuoricatalogo'
   when ioi.qinris =
    greatest(
      ioi.qevasa, ioi.qfuoricat,
      ioi.qinris, ioi.qnonpub, ioi.qann, ioi.qord, ioi.qpren,
      ioi.qrichpag, ioi.qinriass, ioi.qinesa )
    then 'in ristampa'

    -- ...

    end
FROM
  ordiniitem ioi
  group by
    ioi.idordine,
    ioi.grupposped,
    ioi.idart,
    ioi.qevasa, ioi.qfuoricat, ioi.qinris,
    ioi.qnonpub, ioi.qann, ioi.qord, ioi.qpren,
    ioi.qrichpag, ioi.qinriass, ioi.qinesa
;

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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?

David J.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ivan Sergio Borgonovo
> Sent: Monday, May 16, 2011 7:39 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] find the greatest, pick it up and group by
>
>
> I'd like to group by idordine, grupposped, idart.
> For every row grouped that way, I'd like to pick the greatest of the
> q* columns and insert:
> idordine, grupposped, idart, name of the greatest(q*) in a new table.
> I don't mind if more than one q* column is equal to greatest(q*). It
should
> pick up one, just one no matter which among the one equal to greatest(q*).
>
> I think this way works but it hurts my eyes. Any alternative approach?
>

On Mon, 16 May 2011 20:05:45 -0400
"David Johnston" <polobo@yahoo.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

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@webthatworks.it> wrote:
> On Mon, 16 May 2011 20:05:45 -0400
> "David Johnston" <polobo@yahoo.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>