Re: how to reset sequences for different sets of records

>> Oracle version 10.2.0.7
>> Windows 2003 Server
>>
>> I have a table where I need to reset and increment a certain column
>> based on the following example
>>
>> table: activity (primary_key not shown)
>> columns
>> parent_id  activity_type_id recno
>> -------------  ---------------------- --------
>> 1                         1             1
>> 1                         1             2
>> 1                         1             3
>> 1                         2             1
>> 1                         2             2
>> 1                         3             1
>> 1                         3             2
>> 2                         2             1
>> 2                         2             2
>> 2                         3             1
>> 2                         4             2
>> 2                         4             3
>> 2                         4             4
>>
>> Basically I have to reset the recno for every combination of parent_id
>> and activity_type_id.

> Maybe a job for an analytic function and possibly a temp table?
>
> CREATE TABLE T1(
> C1 NUMBER,
> C2 NUMBER,
> C3 NUMBER,
> C4 NUMBER,
> PRIMARY KEY (C1));
>
> INSERT INTO T1
> SELECT
> ROWNUM,
> CEIL(ROWNUM/5),
> MOD(ROWNUM,5),
> NULL
> FROM
> DUAL
> CONNECT BY
> LEVEL<=50;
>
> COMMIT;
>
> SELECT
> *
> FROM
> T1
> ORDER BY
> C2,
> C1;
>
> C1 C2 C3 C4
> --- --- --- ---
> 1 1 1
> 2 1 2
> 3 1 3
> 4 1 4
> 5 1 0
> 6 2 1
> 7 2 2
> 8 2 3
> 9 2 4
> 10 2 0
> 11 3 1
> 12 3 2
> 13 3 3
> 14 3 4
> 15 3 0
> ...
>
> SELECT
> C1,
> C2,
> C3,
> C4,
> ROW_NUMBER() OVER (PARTITION BY C2 ORDER BY C3) MY_NUMBER
> FROM
> T1;

Why do you partition only by C2? If I read the requirement correctly the number must be reset for every combination of two columns so that would rather be "partition by c1, c2".

> C1 C2 C3 C4 MY_NUMBER
> --- --- --- --- ----------
> 5 1 0 1
> 1 1 1 2
> 2 1 2 3
> 3 1 3 4
> 4 1 4 5
> 10 2 0 1
> 6 2 1 2
> 7 2 2 3
> 8 2 3 4
> 9 2 4 5
> 15 3 0 1
> ...
>
> Now you just need to find a way to slide the values from the MY_NUMBER
> column into the column C4.

Maybe that's not necessary. Difficult to tell without more knowledge of the use case.

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/