Oracle: create sequence number for repeated rows

Question

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.

Does the SEQ really have to exist as a permanent row in the table, or is it only useful and relevant when the data is queried? And on a related note, what happens if a row is deleted or updated - do you want a gap/duplicate (you'd get a duplicate if the row with ID of 3 had VALUE updated to 100, for example) or the SEQ to be recalculated for all the remaining rows? — Alex Poole
– Alex Poole, Commented Apr 30, 2013 at 11:51
the sequence exists permanent in the table. After the rows are deleted or added, modification of sequence is taken care by the triggers. — user613114
– user613114, Commented May 2, 2013 at 4:58

Florin Ghita · Accepted Answer · 2013-04-30 11:59:33Z

3

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

answered Apr 30, 2013 at 11:59

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle: create sequence number for repeated rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related