Repost

I have a need to update data in a table so that
a column contains the generated number for sets of records
for instance I have fileName as listed and it should
generate the SheetNum for each of the set
FileName SheetNum
A001 1
A001 2
A002 1
A002 2
A002 3
A003 1
A003 2
B011 1
B011 2
B011 3
C189 1
D9090 1
Thank you in advance
-Kamy
.Kamy,
This will do it at least as you specified.
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
GO
CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
GO
INSERT test VALUES ('A001',0)
INSERT test VALUES ('A001',0)
INSERT test VALUES ('A002',0)
INSERT test VALUES ('A002',0)
INSERT test VALUES ('A002',0)
INSERT test VALUES ('A003',0)
INSERT test VALUES ('A003',0)
INSERT test VALUES ('B011',0)
INSERT test VALUES ('B011',0)
INSERT test VALUES ('B011',0)
INSERT test VALUES ('C189',0)
INSERT test VALUES ('D9090',0)
GO
ALTER TABLE dbo.test add Ctr int not null identity(1,1)
GO
SELECT t1.TestCol
, (SELECT COUNT(*) FROM test t2 WHERE t1.testcol = t2.testcol AND t1.Ctr
>= t2.Ctr)
FROM test t1
GO
ALTER TABLE dto.test DROP COLUMN Ctr
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Kamy" <anonymous@.discussions.microsoft.com> wrote in message
news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
> I have a need to update data in a table so that
> a column contains the generated number for sets of records
> for instance I have fileName as listed and it should
> generate the SheetNum for each of the set
> FileName SheetNum
> A001 1
> A001 2
> A002 1
> A002 2
> A002 3
> A003 1
> A003 2
> B011 1
> B011 2
> B011 3
> C189 1
> D9090 1
> Thank you in advance
> -Kamy
> .
>|||Thank you Ron,
I will try it out.
-Kamy
>--Original Message--
>Kamy,
>This will do it at least as you specified.
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>> Thank you in advance
>> -Kamy
>> .
>>
>
>.
>|||hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy
>--Original Message--
>Kamy,
>This will do it at least as you specified.
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>> Thank you in advance
>> -Kamy
>> .
>>
>
>.
>