CODEWITHSUNDEEP
oracle-databasesequencerepeat
user613114
user613114

Reputation: 2821

Oracle: create sequence number for repeated rows

I have a table with 3 columns:

ID  VALUE   SEQ

1   100     

2   100     

3   200     

4   200     

5   200

Whenever value in column "VALUE is repeated in further rows, value in column "SEQ" must be incremented by 1. Expected output is:

ID  VALUE   SEQ

1   100     1

2   100     2

3   200     1

4   200     2

5   200     3

As per my understanding it can not be done in insert query while filing data in to this table. It has to be done using post-processing may be. I guess it can be done using analytical function using "row_number() and partition by", but not sure how to do it. Please let me know, if it can be done using some better way. Any hint will be appreciated. Thanks.

Upvotes: 1

Views: 4089

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

select id, value, row_number() over (partition by value order by id) as seq
from your_table;

Upvotes: 3

Related Questions