Take the 2-minute tour ×

I have a table with 3 columns:


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:


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.

share|improve this question
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 Apr 30 '13 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 May 2 '13 at 4:58
add comment

1 Answer

up vote 2 down vote accepted
select id, value, row_number() over (partition by value order by id) as seq
from your_table;
share|improve this answer
Thanks Florin, This works as expected. –  user613114 May 2 '13 at 4:57
add comment

Your Answer


