Re: how to reset sequences for different sets of records
Date: Tue, 15 Sep 2009 07:11:18 -0700 (PDT)
Message-ID: <36581732-2aac-4992-bcd0-0110dd35e06c_at_g23g2000vbr.googlegroups.com>
On Sep 14, 6:54 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Sep 14, 5:18 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
>
>
>
>
> > On 14.09.2009 22:19, Charles Hooper wrote:
> > >> 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
>
> Good catch - thanks. In this test case the column C1 was supposed to
> be the OP's non-printed primary key column. In that case, I think
> that it should actually be partitioned on C2 and C3. Unfortunatly,
> the formula that I used to populate C3 did not add repeated values for
> any of the C2 values. Might need to change MOD(ROWNUM,5) to CEIL(MOD
> (ROWNUM,5)/2) for a correct test set up.
>
> Now how would the OP perform an update without creating a temporary
> table to hold the output of the ROW_NUMBER function, and without using
> PL/SQL...
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
There is an as-yet unspecified primary key on this table:
"table: activity (primary_key not shown) "
Using that information should make it fairly easy to effect the update:
SQL> create table activity(
2 act_key number primary key, 3 parent_id number, 4 activity_type_id number, 5 recno number
6 );
Table created.
SQL>
SQL> insert all
2 into activity
3 values(1,1,1,1 )
4 into activity
5 values(2,1,1,2 )
6 into activity
7 values(3,1,1,3 )
8 into activity
9 values(4,1,2,1 )
10 into activity
11 values(5,1,2,2 )
12 into activity
13 values(6,1,3,1 )
14 into activity
15 values(7,1,3,2 )
16 into activity
17 values(8,2,2,1 )
18 into activity
19 values(9,2,2,2 )
20 into activity
21 values(10,2,3,1 )
22 into activity
23 values(11,2,4,2 )
24 into activity
25 values(12,2,4,3 )
26 into activity
27 values(13,2,4,4 )
28 select * from dual;
13 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select parent_id, activity_type_id, recno
2 from activity;
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
PARENT_ID ACTIVITY_TYPE_ID RECNO
---------- ---------------- ----------
2 4 3
2 4 4
13 rows selected.
SQL>
SQL> select act_key, parent_id, activity_type_id, row_number() over
(partition by parent_id, activity_type_id order by parent_id,
activity_type_id) rn
2 from activity;
ACT_KEY PARENT_ID ACTIVITY_TYPE_ID RN
---------- ---------- ---------------- ----------
1 1 1 1
2 1 1 2
3 1 1 3
4 1 2 1
5 1 2 2
6 1 3 1
7 1 3 2
8 2 2 1
9 2 2 2
10 2 3 1
11 2 4 1
ACT_KEY PARENT_ID ACTIVITY_TYPE_ID RN
---------- ---------- ---------------- ----------
12 2 4 2
13 2 4 3
13 rows selected.
SQL>
SQL> update activity a
2 set a.recno =
3 (select rn from
4 (select act_key, parent_id, activity_type_id,
5 row_number() over (partition by parent_id,
activity_type_id order by parent_id, activity_type_id) rn
6 from activity) b
7 where b.parent_id = a.parent_id
8 and b.activity_type_id = a.activity_type_id
9 and b.act_key = a.act_key);
13 rows updated.
SQL>
SQL> select parent_id, activity_type_id, recno
2 from activity;
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 1
PARENT_ID ACTIVITY_TYPE_ID RECNO
---------- ---------------- ----------
2 4 2
2 4 3
13 rows selected.
SQL> David Fitzjarrell Received on Tue Sep 15 2009 - 09:11:18 CDT