Update Sequence In Table – Oracle – SQL - SQL Questions

I have a table:

table1

u_a_id element_id my_seq line_num 1 HI01-01 1 30 1 HI01-02 1 30 1 HI01-01 1 31 1 HI01-02 1 31 1 HI02-03 1 31 1 HI02-04 1 31 2 HI01-01 1 40 2 HI01-02 1 40 2 HI02-01 1 40 2 HI02-02 1 40 2 HI02-03 1 40 2 HI02-04 1 40 2 HI03-02 1 41 2 HI03-03 1 41 2 HI05-04 1 41 2 HI05-05 1 41

I need to update my_seq if a new HI01 appears in the same u_a_id or the counter for HI changes, for ex. HI01 -> HI02 for each u_a_id order by line_num.

I have this query, however this gives seq as 1 even for second instance of HI01-01 in u_a_id = 1:

select t.*, dense_rank() over (partition by u_a_id order by substr(element_id, 1, 4)) as new_my_seq from table1 t

The output would look like:

u_a_id element_id my_seq line_num 1 HI01-01 1 30 1 HI01-02 1 30 1 HI01-01 2 31 1 HI01-02 2 31 1 HI02-03 3 31 1 HI02-04 3 31 2 HI01-01 1 40 2 HI01-02 1 40 2 HI02-01 2 40 2 HI02-02 2 40 2 HI02-03 2 40 2 HI02-04 2 40 2 HI03-02 3 41 2 HI03-03 3 41 2 HI05-04 4 41 2 HI05-05 4 41

Is there a way in Oracle SQL to achieve this?

Advertisement

Answer

I think you just want:

select t.*, dense_rank() over (partition by u_a_id order by line_num, substr(element_id, 1, 4) ) as my_seq from t;