How to create sequence number based on a condition of another table?

Question

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

Column ID and C_Seq_No are from one table (A)
D_Seq_No and Type are from another table (B).

I'm trying to join the 2 tables together where anytime C_Seq_No shows up with the number "1" three times for one ID, then D_Seq_No would create a sequence number of 1, 2, and 3 for that ID.

Here is what I have when I run the query to join the 2 table since I haven't assigned anything to the D_Seq_No column yet:

      TABLE A                          TABLE B
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 query resultset to look like when joining those 2 tables:

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

What query is creating that result? Because if you have two tables, there isnt any join to generate that. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 30, 2016 at 21:07

Gordon Linoff · Accepted Answer · 2016-11-30 21:04:52Z

2

You are looking for row_number. The call would look like this:

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

answered Nov 30, 2016 at 21:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

EkansDasnakE Over a year ago

Could that be done if id and c_seq_no are from another table? we can't use join statement right?

Gordon Linoff Over a year ago

@EkansDasnakE . . . There is no requirement that the columns come from the same table.

Collectives™ on Stack Overflow

How to create sequence number based on a condition of another table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related