RE: Renumber a set of grupped rows?

At 06:49 PM 1/20/2004, you wrote:
>Not pretty but it should work:

It does, thanks!

maa

>SQL> create or replace procedure emprec
> 2 is
> 3 cursor empcursor is
> 4 select empno, recno from emptest order by empno for update of
>recno;
> 5 v_empno number := 99999;
> 6 v_count number := 0;
> 7 begin
> 8 for x in empcursor loop
> 9 if x.empno <> v_empno then v_count:=1;
> 10 v_empno := x.empno;
> 11 end if;
> 12 update emptest
> 13 set recno = v_count
> 14 where current of empcursor;
> 15 v_count:=v_count+1;
> 16 end loop;
> 17 end emprec;
> 18 /
>
>Procedure created.
>
>SQL> execute emprec;
>
>PL/SQL procedure successfully completed.
>
>SQL> commit;
>
>Commit complete.
>
>SQL> drop procedure emprec;
>
>Procedure dropped.
>
>
>
>--Jeff
>
>-----Original Message-----
>Sent: Tuesday, January 20, 2004 3:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>I have a 10000-rows table with 2 columns, Emp_ID and Req_ID.
>
>There are about 150 different emp_ids in these 10000+ records.
>
>What I want to do is the following:
>
>For every different Emp_id, I need the Rec_ids that corresponds to it
>to be updated/renumbered starting from 1 and keep going up by 1.
>
>So I want it to look something like this:
>
>Emp_ID Req_ID
>
>10001 001
>10001 002
>10001 003
>10001 004
>10001 005
>10001 006
>10001 007
>10001 008
>
>
>10002 001
>10002 002
>10002 003
>10002 004
>10002 005
>
>
>10003 001
>
>10004 001
>10004 002
>10004 003
>10004 004
>10004 005
>10004 006
>
>
>etc
>
>
>Any ideas?
>
>Thanks,
>maa
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Maryann Atkinson
> INET: maryann_30_at_yahoo.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Eberhard, Jeff
> INET: Jeff.Eberhard_at_Rolls-RoyceGS.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Maryann Atkinson
  INET: maryann_30_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).