Update sequence in table - Oracle

Question

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?

Isn't that the same as your other two questions: stackoverflow.com/questions/56418550/… and stackoverflow.com/questions/56418904/… — user330315
– user330315, Commented Jun 3, 2019 at 8:01
Requirements changed and the person asked to create a new question. — dang
– dang, Commented Jun 3, 2019 at 8:02
@dang . . . SQL tables represent unordered sets. Your result seems to require having an ordering columns, which is not clear in the data. — Gordon Linoff
– Gordon Linoff, Commented Jun 3, 2019 at 10:46
@GordonLinoff - the ordering is - u_a_id, line_num, element_id ASC — dang
– dang, Commented Jun 3, 2019 at 10:48

Gordon Linoff · Accepted Answer · 2019-06-03 10:54:01Z

2

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;

edited Jun 3, 2019 at 10:54

answered Jun 3, 2019 at 10:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jervs · Accepted Answer · 2019-06-03 08:09:34Z

0

You may want to order by line_num instead of substr(element_id, 1, 4) and place substr(element_id, 1, 4) in partition by.

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

answered Jun 3, 2019 at 8:09

Jervs

3441 silver badge12 bronze badges

4 Comments

dang Over a year ago

It is giving wrong result. For HI02-01, it is showing sequence as 1 whereas it should show 3.

dang Over a year ago

The count is always resetting when HI01 changes to HI02 and so on.

Jervs Over a year ago

Your requirement is not clear. In your post above your HI02-01 is supposed to be 2. You may want to play with partition and order by to get your desired output.

dang Over a year ago

Not sure what requirement is not clear. I have shared the input table and expected output. I have tried modifying the logic, but am unable to get the required result.

Collectives™ on Stack Overflow

Update sequence in table - Oracle

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related