newbie pl/sql question

From: <mdbluecrab_at_adelphia.net>
Date: 7 Mar 2007 08:57:16 -0800
Message-ID: <1173286636.207306.115150@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 Received on Wed Mar 07 2007 - 10:57:16 CST