giving identity value based on data output

• td-39866

  SSC Veteran

  Points: 249

  More actions

  July 8, 2004 at 10:56 am

  #88159

  I have a data output as such:

  location_id job_index job_id     

  ----------- --------- -----------

  5           NULL      2372

  7           NULL      2188

  11          NULL      2186

  11          NULL      2253

  19          NULL      2427

  31          NULL      2314

  31          NULL      2315

  32          NULL      2354

  36          NULL      2209

  36          NULL      2310

  36          NULL      2353

  36          NULL      2393

  what is the best way to generate the following output instead:

  location_id job_index job_id     

  ----------- --------- -----------

  5           1      2372

  7           1      2188

  11          1      2186

  11          2      2253

  19          1     2427

  31          1      2314

  31          2      2315

  32          1     2354

  36          1      2209

  36          2      2310

  36          3      2353

  36          4      2393

  Any help or suggestions are greatly appreciated.

• AKM

  SSCommitted

  Points: 1538

  More actions

  July 9, 2004 at 3:10 am

  #514015

  Try this

  update tablename T

  set job_index = (select count(job_id) from tablename T2 where T.location=T2.location and T.job_id<T2.job_id) + 1

  Here we are saying 'for all rows, set job_index to (the number of rows with the same location but a numerically lower job_id) plus 1'