Re: Renumber a set of grupped rows?

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).