serial no in sql server 2000 on certain criteria

• Ghulam Farid

  Grasshopper

  Points: 11

  More actions

  July 18, 2011 at 11:59 am

  #240949

  dear all

  i have following table in sql server 2000

  create table test (id int, batch varchar(5))

  insert into test values (1,abc)

  insert into test values (1,abc)

  insert into test values (2,abc)

  insert into test values (2,abc)

  insert into test values (3,abc)

  insert into test values (1,xyz)

  insert into test values (1,xyz)

  insert into test values (2,xyz)

  insert into test values (2,xyz)

  insert into test values (3,xyz)

  insert into test values (1,pqr)

  insert into test values (2,pqr)

  insert into test values (2,pqr)

  i want to assign sno to this data on following criteria

  sno id batch

  1 1 abc

  2 1 abc

  3 2 abc

  4 2 abc

  5 3 abc

  1 1 xyz

  2 1 xyz

  3 2 xyz

  4 2 xyz

  5 3 xyz

  1 1 pqr

  2 2 pqr

  3 2 pqr

  that sno restarts when batch no changes and sno restat from min id no

  how to apply that

  regards

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  July 19, 2011 at 9:03 am

  #1354878

  Is this what you are looking for?

  create table #test (id int, batch varchar(5))

  insert into #test values (1,'abc')

  insert into #test values (1,'abc')

  insert into #test values (2,'abc')

  insert into #test values (2,'abc')

  insert into #test values (3,'abc')

  insert into #test values (1,'xyz')

  insert into #test values (1,'xyz')

  insert into #test values (2,'xyz')

  insert into #test values (2,'xyz')

  insert into #test values (3,'xyz')

  insert into #test values (1,'pqr')

  insert into #test values (2,'pqr')

  insert into #test values (2,'pqr')

  select *, ROW_NUMBER() over (partition by batch order by batch, ID) as RowNum

  from #test

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/