Incrementing suffix for unique id

[PreacherUK]

Hi Chaps/Chapettes

Heres what I'm trying to do:

ID_number
507119
507119
507119
506225
506225
703665
703665
703665

We have a system (third party vendor, so we can't make changes to it)
that ids a bulk transaction by a number, for instance 507119. There may
be many allocations/sub transactions. These however do not have a unique
suffix (a quirk of the third party software).

I am looking (preferably) for a set based solution to assigning suffixes
to the above list of ids in the following way:

ID_number Suffix
507119 01
507119 02
507119 03
506225 01
506225 02
703665 01
703665 02
703665 03


So the suffixes increment but only within each of the ids.

I can do this readily enough in MSAccess/VBA with a record set, but was
hoping there was a set based solution.

Many thanks for any help

 

[vongrunt]

Wanna do that set-based way, each row must have something unique (one or more columns) to catch on.

With that in mind, here is general idea:

create table blah (ID_number int)
insert into blah(ID_number) values (507119)
insert into blah(ID_number) values (507119)
insert into blah(ID_number) values (507119)
insert into blah(ID_number) values (506225)
insert into blah(ID_number) values (506225)
insert into blah(ID_number) values (703665)
insert into blah(ID_number) values (703665)
insert into blah(ID_number) values (703665)

alter table blah add rowid int identity(1,1) 
alter table blah add Suffix tinyint

update blah 
set Suffix = (select count(*) from blah B2 where blah.ID_number=B2.ID_number and blah.rowid >= B2.rowid)

select * from blah

drop table blah

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[PreacherUK]

Thanks Von. I'll give it a whirl.