Is there any better way?

[badrinathn]

Hi,
What I am trying is to, redo the sequence numbers of my data to remove gaps.

create sequence rtseq increment by 1;

create or replace view ldr1view as select name, seq from lrs_test order by name, seq;

drop table lrs8;

create table lrs8 as select ldr1view.*, rtseq.nextval sequence from ldr1view;

update lrs_test u set seq =
(select (a.sequence - minseq.minval) +1
from lrs8 a, (select name, min(sequence) minval from lrs8 group by name) minseq where u.name = a.name and u.seq = a.seq and a.name = minseq.name);
This seems to be working, but is there a better way to do this?

Please let me know.

badrinath

[marist89]

I have a headache now.

SQL> select * from xyz;

        ID DT
---------- ---------
         3 29-JAN-04
         1 29-JAN-04
         5 29-JAN-04
        30 29-JAN-04

SQL> update xyz set id=rownum;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from xyz;

        ID DT
---------- ---------
         1 29-JAN-04
         2 29-JAN-04
         3 29-JAN-04
         4 29-JAN-04

[slimdave]

Originally posted by badrinathn
Hi,
What I am trying is to, redo the sequence numbers of my data to remove gaps.

Would you care to amuse us by sharing your rationale for this requirement?

[jodie]

Originally posted by slimdave
Would you care to amuse us by sharing your rationale for this requirement?

Ugh! Didn't we just go through this recently!

[badrinathn]

Hi

was off the oracle for some time...and didn't get to follow the other threads...I am sure this has already come up...but

Eg:
name seq_no
Test 5
Test 11
Test 22
Test 25
Test 26
Otest 1
Otest 8
Otest 10
Otest 11


Should become:

name seq_no
Test 1
Test 2
Test 3
Test 4
Test 5
Otest 1
Otest 2
Otest 3
Otest 4

[balajiyes]

This query will give u the expected result.

select name, row_number() over (partition by name order by name) from test;

[badrinathn]

Hi :

That was cool, I had a bit dumbish plsql for this as
SQL> declare
2 numb number;
3 name varchar2(30);
4 cursor c1 is select name,count(*) cnt from lrs_test group by name;
5 begin
6 open c1;
7 loop
8 fetch c1 into name, numb;
9 exit when c1%notfound;
10 for i in 1..numb loop
11 insert into lrs_test1 (name,seq) values (name, i);
12 end loop;
13 end loop;
14 close c1;
15 end;
16 /
but your tip was great.!!