Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> group by is very slow .. is there different way
I try to write select SQL for table1 & table2 to make it like
table_result
The idea takes the rows dependent on ID and TYPE and maximum amount to take VALUE
table2
FK TYPE VALUE AMOUNT 1111 A 3 18 1111 A 2 21 1111 B 3 32 2222 B 2 43 2222 B 4 53 2222 C 2 23 2222 G 1 45 3333 D 6 22 3333 A 4 66 3333 A 3 54 3333 A 6 76 3333 A 2 44 3333 G 3 99 table1 PK ID NAME 1111 110011 aaaaaa 2222 220022 bbbbbb 3333 330033 cccccc table_result ID TYPE VALUE AMOUNT 110011 A 2 21 110011 B 3 32 220022 B 4 53 220022 C 2 23 220022 G 1 45 330033 D 6 22 330033 A 6 76 330033 G 3 99
This is my query put the problem of this query is very slow. Is there any other idea?
SELECT
table1.ID, table1.NAME, MAX(CASE WHEN table2.TYPE='A' THEN table2.AMOUNT END) A, MAX(CASE WHEN table2.TYPE='B' THEN table2.AMOUNT END) B, MAX(CASE WHEN table2.TYPE='C' THEN table2.AMOUNT END) C, MAX(CASE WHEN table2.TYPE='D' THEN table2.AMOUNT END) D, MAX(CASE WHEN table2.TYPE='G' THEN table2.AMOUNT END) G FROM table1 left outer join table2 on (table1.PK=table2.FK AND (table2.FK, table2.AMOUNT, table2.TYPE) IN ( SELECT table2.FK, MAX(table2.AMOUNT), table2.TYPE FROM table1, table2 WHERE table1.PK=table2.FK GROUP BY table2.FK, table2.TYPE )
table1.ID, table1.NAMEReceived on Sun Dec 09 2007 - 06:11:39 CST
![]() |
![]() |