Oracle Sql: add column which counts the rows, starting at 1 each time anothers column value changes -

i guess 1 discussed already, didn`t find it. new sql using oracle sql developer.

here example of problem: example

at moment depot_id unique primary key generated sequence , trigger before inserting data. instead of approach want create table have new column counts ascending starting 1 each time second column in example has new value. how create third column in example? (then want drop first column , use second , third column composite primary key, fail @ creating column 3)

thank tips, alex

you use row_number() function:

select t.*,        row_number() on (partition foreign_key_1 order depot_id) new_column t; 

although answered question, should review guidelines on how ask questions. in particular:

• try include relevant information in question. so, sample data , desired results should in question text.
• try not depend on off-site references make question understandable. can used further illustrate points, question should stand on own. important because other site might disappear 1 day.
• try not depend on pictures key information. makes difficult index content of question.
• and, welcome stack overflow.