Re: newbie pl/sql question

<mdbluecrab_at_adelphia.net> a écrit dans le message de news: 1173286636.207306.115150_at_c51g2000cwc.googlegroups.com...
| I'm fairly new to pl/sql, and I'm having some problems. I'm trying to
| process a list of records, group them based upon CL_KEY and then add a
| sequence, based upon OPTION.
|
| CL_ID CL_KEY OPTION
| ------- ---------- ------
| 1234567 abcdef 999
| 1234567 abcdef 998
| 1234567 abcdef 997
|
| 1234568 abcdeg 999
| 1234568 abcdeg 997
| 1234568 abcdeg 995
|
|
| I can group on either CL_ID, or CL_KEY, order by OPTION, and I would
| like to add a 2 digit seq number CL_ID_SEQ. CL_ID_SEQ should reset to
| '00' for each new CL_ID.
| The OPTION column may have gaps in between numbers.
|
| CL_ID CL_KEY OPTION CL_ID_SEQ
| ------- ---------- ------ ---------
| 1234567 abcdef 999 00
| 1234567 abcdef 998 01
| 1234567 abcdef 997 02
|
| 1234568 abcdeg 999 00
| 1234568 abcdeg 997 01
| 1234568 abcdeg 995 02
|
|
| Any ideas?
|
|
| Thanks
|

select cl_id, cl_key, option,

     to_char(row_number() over (partition by cl_id order by option desc),'fm00') cl_id_seq from mytable
/

Regards
Michel Cadot Received on Wed Mar 07 2007 - 11:03:53 CST