Обсуждение: 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 >