I have this code which simply gives me the count of term. SELECT OUA_ID ,COALESCE (MAX( DECODE( TERM, 0603, TERM_COUNT, NULL ) ), 0 ) "TERM_0603_CNT",COALESCE (MAX( DECODE( TERM, 0702, TERM_COUNT, NULL) ), 0 ) "TERM_0702_CNT",COALESCE (MAX( DECODE( TERM, 0705, TERM_COUNT, NULL ) ), 0 ) "TERM_0705_CNT",COALESCE (MAX( DECODE( TERM, 0707, TERM_COUNT, NULL ) ), 0 ) "TERM_0707_CNT" FROM(SELECT DISTINCTOUA_ID, TERM_COUNT, TERM FROM(SELECT OUA_ID, TERM ,PROVIDER_CDE, COUNT(*) TERM_COUNTFROM TABLE WHERE PROVIDER_CDE = 'BILL'GROUP BY OUA_ID, TERM, PROVIDER_CDE)) GROUP BY OUA_ID
Gives:OUA_ID TERM_0603_CNT TERM_0702_CNT TERM_0705_CNT TERM_0707_CNT32710 0 3 7 045726 2 0 1 0
Is there a way i could used the rank function (with decode) to only display the 1ST (First) and 2nd(Second) most used term per OUA_ID?So i get this:OUA_ID 1st MOST USED 2nd MOST USED 3271 705 702 45726 603 705
Rank most popular/most used term?
Posted - 2012-03-25 : 23:24:32
