Re: rownum & group by question

From: Anurag Varma <avoracle_at_gmail.com>
Date: 22 Mar 2006 09:21:38 -0800
Message-ID: <1143048098.577632.57210@u72g2000cwu.googlegroups.com>

Something like this:
ORA92> select * from tpivot;

    CLT_ID TX_YE AMT
---------- ---------- ----------

         1       2006         10
         1       2006         20
         1       2005          8
         1       2004         20
         1       2003        400
         2       2006        100
         2       2005        200
         2       2005        100
         2       2004          8
         2       2003          7

10 rows selected.

ORA92>
ORA92> select rownum, t.*
  2 from (select CLT_ID, TX_YE, sum(AMT) sum_amt

  3        from tpivot
  4        where clt_id = 1
  5        group by CLT_ID, TX_YE
  6        order by clt_id, tx_ye desc) t

  7 where rownum < 3
  8 /

    ROWNUM CLT_ID TX_YE SUM_AMT
---------- ---------- ---------- ----------

         1          1       2006         30
         2          1       2005          8

ORA92>
ORA92> select t.clt_id, max(tx_ye) max_tx_ye,

  2                   sum(decode(rownum, 1, t.sum_amt)) y1,
  3                   sum(decode(rownum, 2, t.sum_amt)) y2
  4  from (select CLT_ID, TX_YE, sum(AMT) sum_amt
  5        from tpivot
  6        where clt_id = 1
  7        group by CLT_ID, TX_YE
  8        order by clt_id, tx_ye desc) t

  9 where rownum < 3
 10 group by t.clt_id
 11 /

    CLT_ID MAX_TX_YE Y1 Y2
---------- ---------- ---------- ----------

         1 2006 30 8 Received on Wed Mar 22 2006 - 11:21:38 CST