Re: how to reset sequences for different sets of records

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 14 Sep 2009 13:19:36 -0700 (PDT)
Message-ID: <6d8e4511-4a65-465a-a5c8-f525426c00dd_at_z28g2000vbl.googlegroups.com>

On Sep 14, 3:39 pm, Wallyraju <wallyr..._at_gmail.com> wrote:
> Oracle version 10.2.0.7
> Windows 2003 Server
>
> All,
>
> 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.

(Snip)
>
> Any help to solve this issue would be appreciated.
>
> Thanks in advance.
>
> Wally

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;

 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.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Sep 14 2009 - 15:19:36 CDT