find the greatest, pick it up and group by

[Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general