CODEWITHSUNDEEP
sqlsql-serversql-server-2012sequencealter-table
EkansDasnakE
EkansDasnakE

Reputation: 67

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

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.

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

Upvotes: 0

Views: 2732

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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 . . .

Upvotes: 2

Related Questions