How to read data from table and assign sequence number?

Question

I am trying to create add a column to a table but i'm stuck trying to figure out a way to assign sequence number to this column.

Here is what I have:

ID           C_Seq_No        D_Seq_No         Type
123              1             NULL            02
123              1             NULL            04
123              1             NULL            06
123              2             NULL            03
123              2             NULL            05
123              2             NULL            07

This is what I want in my table:

ID           C_Seq_No        D_Seq_No         Type
123              1             1               02
123              1             2               04
123              1             3               06
123              2             1               03
123              2             2               05
123              2             3               07

Thank you for your help!

do you have to update the table?

Vamsi Prabhala
– Vamsi Prabhala

2016-11-16 22:14:42 +00:00
Commented Nov 16, 2016 at 22:14 — Vamsi Prabhala
– Vamsi Prabhala, Commented Nov 16, 2016 at 22:14

Gordon Linoff · Accepted Answer · 2016-11-16 22:15:00Z

4

You are looking for row_number():

select t.*,
       row_number() over (partition by id, c_seq_no order by type) as d_seq_no
from t;

If you actually want an update, you can use an updatable CTE:

with toupdate as (
      select t.*,
             row_number() over (partition by id, c_seq_no order by type) as new_d_seq_no
      from t
     )
update toupdate
    set d_seq_no = new_d_seq_no;

answered Nov 16, 2016 at 22:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to read data from table and assign sequence number?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related