SQL Server - How can I retrieve an ascending count-up of each value in a table, per row? -

not sure how i'd describe want, have table following values:

id goodsnumber 1  700 2  701 3  700 4  700 5  701 

how can retrieve id numbers (and associated goodsnumbers), along count of occurance of goodsnumber (ordered id), follows:

id goodsnumber count 1  700         1 2  701         1 3  700         2 4  700         3 5  701         2 

i've tried following doesn't work - gives me total count on each row:

select a.id, a.goodsnum, b.count tbl inner join (select goodsnum, count(goodsnum) count tbl group goodsnum) b on a.goodsnum = b.goodsnum 

sql 2008+ use row_number()

select id,        goodsnumber,        row_number() on (partition goodsnumber                            order id) [count] yourtable order id 

to add add subquery

select * (     select id,            goodsnumber,            row_number() on (partition goodsnumber                                order id) [count]     yourtable     ) t  id = <yourvariable>