Generating sequences for subsets of a table

Question

I have a table in SqlServer 9 with data of the form

Code    Number
J       0
J       5
J       8
Y       2
Y       8

I'd like to add a column that has a number with the internal sequence of numbers for each code, so that my table looks like this;

Code    Number   Seq
J       0        1
J       5        2
J       8        3
Y       2        1
Y       8        2

Does anyone have any suggestions as to the best way to accomplish this?

Answer 1

look up the functions row_number() and RANK() in Books On Line

Answer 2

Look at the ROW_NUMBER() function.

DECLARE @Number TABLE (
    Code        nvarchar(1)
,   Number      int
)


INSERT @Number VALUES ('J', 0)
INSERT @Number VALUES ('J', 5)
INSERT @Number VALUES ('J', 8)
INSERT @Number VALUES ('Y', 2)
INSERT @Number VALUES ('Y', 8)


SELECT * FROM @Number


SELECT  Code
,       Number
,       ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS Seq
FROM    @Number