find the greatest, pick it up and group by

Поиск
Список
Период
Сортировка
От Ivan Sergio Borgonovo
Тема find the greatest, pick it up and group by
Дата
Msg-id 20110517013840.28193a28@dawn.webthatworks.it
обсуждение исходный текст
Ответы Re: find the greatest, pick it up and group by
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Vibhor Kumar
Дата:
Сообщение: Re: Suppress "INSERT x x" messages
Следующее
От: Ivan Sergio Borgonovo
Дата:
Сообщение: Re: COPY complaining about unquoted carriage return found in data... in a quoted field