asked
generate Autonumber depending on a field
This is the desired output: Obj1 Obj2 0001 ijk-000 0001 ijk-000 0001 ijk-000 0001 ijk-000 0001 ijk-000 0002 ijk-001 0002 ijk-001 0002 ijk-001 0002 ijk-001 But I am getting the following: Obj1 Obj2 0001 ijk-000 0001 ijk-001 0001 ijk-002 0001 ijk-003 0001 ijk-004 0002 ijk-005 0002 ijk-006 0002 ijk-007 0002 ijk-008 Can you please fix my query? Based upon the Obj1 column, Obj2 value should get incremented. select Obj2 ,'IJK-' + convert(varchar,NUM) as NameNew from (select a.* ,(select max(SUBSTRING(NAME,5,len(NAME))) from table1 where name like 'IJK-%' ) + a.[Seq Id] as NUM from table2 A ) c Obj1 is another column in the table2.
@grant Frichey Can you please Fix My Query that should give me the desired Result. Based Upon the Obj1 COlumn Obj2 Value Should get Incremented. select Obj2, 'IJK-' + convert(varchar,NUM) as NameNew from (select a.*, (select max(SUBSTRING(NAME,5,len(NAME))) from table1 where name like 'IJK-%') + a.[Seq Id] as NUM from table2 A) c Obj1 is an other column in the table2. Thanks.
I think what you want to look at is the following windowing function: DENSE_RANK() OVER(PARTITION BY [] ORDER BY []) There's a good explanation of it here:
http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(DENSE_RANK_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true
16 People are following this question.
