Create SQL 2000 View that assigns row number to each row

I need a SQL 2000 View that assigns a row number to each record. In later versions of SQL, this can be accomplished using the WITH statement. Unfortunately, this feature did not exist for this version. The statement would work something like this: select id, product_code, ROWNUM from subscriptions where prod_type = 'chapt' and status = 'a' The challenge is creating that ROWNUM field, or whatever you'd like to call it. The value should start with 1 and increment by 1 for each new row. When the id value changes, the rownum should start back with 1 and increment by 1. For example: id product_code rownum 1025 gold 1 1025 nregion 2 1025 florida 3 1036 southeast 1 1036 magazine 2