Renumber problem. 2

[Jamfool]

Create Table mytable
(
AlphaNumericID  varchar(10),
renumber  int
)
insert into mytable values ('ZAA0062000',null)
insert into mytable values ('AA00062000',null)
insert into mytable values ('AA00062000',null)
insert into mytable values ('AA00062000',null)
insert into mytable values ('AGF0022000',null)
insert into mytable values ('AGF0022000',null)
insert into mytable values ('AGF0022000',null)
insert into mytable values ('AGF0022000',null)
insert into mytable values ('AG03000030',null)

Hi, how do i sequence the renumber column to provide

ZAA0062000 1
AA00062000 1
AA00062000 2
AA00062000 3
AGF0022000 1
AGF0022000 2
AGF0022000 3
AGF0022000 4
AG03000030 1

That is to say each set of alphanumeric ids must have a renumber column of 1 to X

 

[hmckillop]

try this

declare @v_LastValue varchar(10), @v_Counter int

select @v_LastValue = '', @v_counter = 1
update mytable
SET @v_Counter = 
CASE WHEN @v_LastValue <> AlphaNumericID THEN 1 ELSE @v_Counter + 1 END,
renumber = @v_Counter,
@v_LastValue = AlphaNumericID
	
select * from mytable

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Welshbird]

I've use this bit of code before to do a similar thing:

-- The RecID is just an identity column and the CompID basically will
-- be a count of the number of records
-- given to that AlphaNumericID

create table NewTable
(
	RecID integer not null identity (1,1),
	AlphaNumericID  varchar(10),
	renumber  int
)
go

-- Then bung your data in

-- Now the data is there and in order of AlphNumericID
-- we can do some clever stuff to calculate renumber

update NewTable
set Renumber = RecID
go

update NewTable
set Renumber = 
		( select count(*) from NewTable as A
			where A.Renumber <= NewTable.renumber
			and A.AlphNumericID = NewTable.AlphaNumericID)
go

Have to confess haven't had a chance to test it with your data - but let me know if it does what you need.

Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[Jamfool]

Both work well. hmckillop is the one i will use because i dont need to add the identity column, but thanks to you both. star-tastic