CODEWITHSUNDEEP
sqldatabaseoracle10g
fazlook1
fazlook1

Reputation: 139

Oracle column number increase

I would like to know how to increase the row number by 1 in Column 1 when Column 2 value changes in Oracle

What I am looking for is to achieve this : 

 COL1   COL2    COL3 |
 1      2000    xx   |
 1       2000   xy   |
 1      2000    xyz  |
 2      3020    x    |
 2      3020    xiii |
 3      5666666   ueueu

Any idea ?

Upvotes: 0

Views: 73

Answers (2)

yamny
yamny

Reputation: 660

If you want to increase col1 value after updating col2 on table t_ then you can use trigger. 

CREATE OR REPLACE TRIGGER upcol1
  AFTER UPDATE ON t_ FOR EACH ROW
  WHEN (old.col2 != new.col2)
BEGIN
  UPDATE t_ SET col1=:new.col1+1
    WHERE col2=:new.col2 AND col3=:new.col3;
END;

Upvotes: 0

user330315
user330315

Reputation:

I think you are looking for a window function:

select row_number() over (partition by col2 order by col3) as col1,
       col2, 
       col3
from the_table;

Upvotes: 2

Related Questions