Re: Renumber a set of grupped rows?

Maryann

The select workes on 8i, but the update part I have to think som time over :-)

[oracle_at_oracle oracle]$ sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 21 01:43:33 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) x from emp
  2 ;

    DEPTNO X
---------- ----------

        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

14 rows selected.

Maryann Atkinson wrote:

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

-- 

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