increment a column value in sql

[Thunder]

I got a this table

Key1, Key2, Number
A BC 1
A BC 3
A BD 5
A BC 1
B BC 7
B BC 1
B BC 1

And i want to say like this

Key1, Key2, Number
A BC 1
A BC 2
A BD 1
A BC 1
B BC 1
B BC 2
B BC 3

I want to reset the number so the first be 1 by 1, and when the key changes, it's reset to 1
Any easy way to do this?

[MFisher]

Do you want to update existing records ? Or is this when inserting new records, to calculate "Number" ?

If you don't get an answer here, maybe try a SQL forum like SQLServerCentral.

[andreas.prouza]

You can do this by RANK

Select key1, key2, RANK() OVER (PARTITION BY key1, key2 ORDER BY key1, key2)
from ...

The PARTITION BY part is responsible for the reset of the counter.