Re: Renumber a set of grupped rows?

 > select deptno, row_number() over (PARTITION BY DEPTNO order by deptno)
 > from emp
 > /

Hm... but thats not available in 8i, is it?

and besides, I want to update the table, not just select from it...

...

thx
maa

At 06:19 PM 1/20/2004, you wrote:
>Maryann
>You can use the new windowing function, here is test select working
>on emp :-)

>select deptno, row_number() over (PARTITION BY DEPTNO order by deptno)
> from emp
>/
>
> DEPTNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYDEPTNO)
>---------- ------------------------------------------------
> 10 1
> 10 2
> 10 3
> 20 1
> 20 2
> 20 3
> 20 4
> 20 5
> 30 1
> 30 2
> 30 3
> 30 4
> 30 5
> 30 6
>
>Maryann Atkinson wrote:
>
>>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
>
>--
>
>Best regards/Venlig hilsen
>
>/*Peter Gram*/ <mailto:peter.gram_at_miracleas.dk>
>
>Miracle A/S <http://www.miracleas.dk/>
>Kratvej 2
>DK - 2760 Måløv
>
>Cell: (+45) 2527 7107
>Phone: (+45) 4466 8855
>Fax: (+45) 4466 8856
>Home: (+45) 3874 5696
>Email: peter.gram_at_miracleas.dk <mailto:peter.gram_at_miracleas.dk>
>
>SQL Server Master Class 8-10 Marts,
>Database Forum 28-30 October
>Master Class 17-19 Januar 2005.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Peter Gram
> INET: peter.gram_at_miracleas.dk
>
>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).