SQL & PL/SQL

Hi All,

Need to generate a sequence number based on a column value in a table.

when trying to insert into a table using order by and rownum, i am not able to achieve the same.

Below is a simple example to show this .. help needed how to insert a sequence field into table base a column value ..

create table test1(col1 number);


insert into test1 values(1);
insert into test1 values(2);




insert into test1 values(4);
insert into test1 values(3);

select * from test1;

1

2

4

3

Now i try to insert into test2 based on test1 values:

create table test2(col1 number,seq number);


insert into test2 (select col1,rownum from test1  order by col1 asc);

ORA-00907: missing right parenthesis

So i tried with :

insert into test2 select col1,rownum from test1  order by col1 asc;

This worked , but the sequence is not generated as per the ordering ...

can anyone please help ..

Thanks in advance ...

MRK ...