adding sequence number in the table

• anjaliagarwal5

  Ten Centuries

  Points: 1267

  More actions

  January 15, 2016 at 12:45 pm

  #323634

  Hi All,

  I have this table that has a key_no and seq_no. The seq_no is missing right now. I was supposed to provide the sequence number form my code which I didn't provide.

  Table A

  Key_no Seq_no Data

  1 0 Test1

  1 0 Test2

  1 0 Test3

  1 0 Test4

  1 0 Test5

  2 0 Test6

  2 0 Test7

  2 0 Test8

  2 0 Test9

  2 0 Test10

  2 0 Test11

  3 0 Test12

  3 0 Test13

  3 0 Test14

  Is there any way, I can put the sequence number through a query right now. The sequence number will increment as long as the the key number is same and as soon as the key number changes, the sequence number will be reset to 1. I want something like this:

  Key_no Seq_no Data

  1 1 Test1

  1 2 Test2

  1 3 Test3

  1 4 Test4

  1 5 Test5

  2 1 Test6

  2 2 Test7

  2 3 Test8

  2 4 Test9

  2 5 Test10

  2 6 Test11

  3 1 Test12

  3 2 Test13

  3 3 Test14

  and so on..

  any help will be appreciated. I have other columns in this table.

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  January 15, 2016 at 12:53 pm

  #1852323

  ROW_NUMBER() is your friend here. Something like:

  select rw = ROW_NUMBER() OVER (PARTITION BY KEY_NO ORDER BY DATA)

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin