Re: how to reset sequences for different sets of records

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 14 Sep 2009 16:54:21 -0700 (PDT)
Message-ID: <2195a702-2226-4f62-9942-763ed2c7675e_at_j19g2000vbp.googlegroups.com>

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. Received on Mon Sep 14 2009 - 18:54:21 CDT