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 по дате отправления: