question

ch.venkatesh514 avatar image
ch.venkatesh514 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.
row_numberautomaticgenerate-sequence
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
can you show us how table1 and table2 looks like ?
0 Likes 0 ·

1 Answer

·
GPO avatar image
GPO answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.