Re: Numbers in Sequence

g wrote:
> ok create a package with 2 cursors
> one to get the names and group by name
> the other to get all the records in the first
>
> then loop throght the 2 cursor with a count and put in the sequence
>
>
>
> "Reiro" <ReiroGP_at_gmail.com> wrote in message
> news:1158325154.342305.38080_at_i42g2000cwa.googlegroups.com...
> >
> >
> >
> > -- its an update ... its gna be once off....
> >

Depending on the version of Oracle there is no need to use pl/sql to do what can be done in a single SQL statement if I did not misunderstand the request.

UT1 > drop table t1;

Table dropped.

UT1 > create table t1 (class varchar2(1), seq_num_col number);

Table created.

UT1 > insert into t1 values ('A',31);

1 row created.

UT1 > insert into t1 values ('A',33);

1 row created.

UT1 > insert into t1 values ('B',37);

1 row created.

UT1 > insert into t1 values ('B',02);

1 row created.
UT1 > insert into t1 values ('B',03);

1 row created.

UT1 > insert into t1 values ('B',06);

1 row created.

UT1 > insert into t1 values ('C',04);

1 row created.

UT1 > insert into t1 values ('C',07);

1 row created.

UT1 > insert into t1 values ('C',08);

1 row created.

UT1 > commit;

Commit complete.

UT1 > create table t2 as
  2 select class, new_seq
  3 from ( select class, row_number()

  4         over (partition by class order by seq_num_col) as new_seq
  5         from t1
  6       );

Table created.

UT1 > select * from t2;

C NEW_SEQ
- ----------

A          1
A          2
B          1
B          2
B          3
B          4
C          1
C          2
C          3

9 rows selected.

UT1 > drop table t1;

Table dropped.

UT1 > drop table t2;

Table dropped.

See the SQL manual section on analytic functions.

HTH -- Mark D Powell -- Received on Fri Sep 15 2006 - 09:01:10 CDT