Update sequence in table – Oracle

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?

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;

Advertisement

Answer