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: 246695

  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)

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.