Need to add one more column with seq

by Nitin Patil
(Pune,maharastra,India)



Hi,
Below is my table and data
create table t11 (id number,property varchar2(20));

insert into t11 values(1,'p1');
insert into t11 values(1,'p2');
insert into t11 values(1,'p3');
insert into t11 values(2,'p1');
insert into t11 values(2,'p2');

I want out in below format.
ID PROPERTY Sequence
1 p1 1
1 p2 2
1 p3 3
2 p1 1
2 p2 2

Could you please suggest some solution in SQL/PL SQL

Comments for Need to add one more column with seq

Average Rating starstarstarstarstar

Click here to add your own comments

Aug 23, 2016
Rating
starstarstarstarstar
analytic function
by: Brian Fitzgerald

select id, property,
row_number() over ( partition by id order by property ) seq
from t11
order by id, seq
;

Aug 23, 2016
Rating
starstarstarstarstar

by: John

You don't say what the sequence should be based on so answering the question fully is a little difficult.

As it stands using your example data you could dispnse with the sequnce column all together and just order the data by id and pproperty like so:


SELECT id, property FROM t11 ODER BY id, property;


If you need an identifier of the sequence in which rows were inserted the you could consider adding a timestamp to your table definition and populating it as data is inserted into the table. You could do this either by using a trigger on the table or preferably by coding it in the insert statement or deining a default value in the table definition..

For example, assuming your table definition changes to:

create table t11 (id number,property varchar2(20), created_on timestamp(9) default systimestamp);

Then the insert statements from before would not need to change.


insert into t11 values(1,'p1');
insert into t11 values(1,'p2');
insert into t11 values(1,'p3');
insert into t11 values(2,'p1');
insert into t11 values(2,'p2');


Alternatively you could use a sequnce which Oracle guarantees to be unique whereas 2 or more timestamp values could be the same. However with sequences you are not huarantee dto get consecutive numbers i.e. some values could be missing.


create table t11 (id number,property varchar2(20), order_of_creation number default propert_seq.nextval);


It all comes down to what you're trying to achieve in your application and why.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.