Repost: Solution didn't work any other ideas??

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
>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[vbcol=seagreen]
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
records[vbcol=seagreen]
You need to update using a query and the PK. That identity would be a useful
PK for this
alter table tbl add seq int
update tbl set seq = (select count(*) from tbl t1 where t1.sheet = t.sheet
and t1.pk <= t.pk)
from tbl t
"Kamy" wrote:

> 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
> t2.testcol AND t1.Ctr
> message
> records
>
|||Nigel,
Thanks for taking time to reply! But this also does not
solve my problem, it increments all numbers in order from
1 to ...
I don't want it to do that, please take a look at my
results again, for a given set I would like it to
increment nos, and then start with 1 again.
Thank you again,
-Kamy

>--Original Message--
>You need to update using a query and the PK. That
identity would be a useful
>PK for this
>alter table tbl add seq int
>update tbl set seq = (select count(*) from tbl t1 where
t1.sheet = t.sheet[vbcol=seagreen]
>and t1.pk <= t.pk)
>from tbl t
>"Kamy" wrote:
dbo.test[vbcol=seagreen]
(1,1)
>.
>