Populate Identity column with sequential numbers restarting count on change of key field I am attempting to populate an Identity column with sequential numbers but I want the count to start over when the value of a key field changes.

Example data and desired ID column results:

Month_  Date_   Year_   SalesRep  ID
10  2018-10-31  2018    AL01      0
10  2018-10-31  2018    AL01      1
10  2018-10-31  2018    AL01      2
10  2018-10-31  2018    AL01      3
10  2018-10-31  2018    AL02      0
10  2018-10-31  2018    AL02      1
10  2018-10-31  2018    AL02      2
10  2018-10-31  2018    AL02      3


The below query does not produce the desired results:

INSERT INTO SALES_INFO_SNAPSHOT_WD_TEST(ID)
SELECT DEL_ID
FROM ( 
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Year_,Month_,Date_,SalesRep ORDER BY 
SalesRep) - 1 As DEL_ID

FROM
Sales_Info_Snapshot_WD_TEST
where date_ = '10/31/2018') D
WHERE DEL_ID IS NOT NULL


Anyone know of a painless solution?

The select query works perfectly but does not insert into the table.
I need to number these rows in order to identify a large chunk of data that needs to be deleted from a very important reporting table.

Thank you.  Any help is appreciated.

Marlene
SSMS v 17.7
So it was suggested that I post the answer I was able to come up with to resolve my issue.  I decided to create a new table to make sure it would work and then save that table as the original table.

SELECT
*, ROW_NUMBER() OVER(PARTITION BY YEAR_,MONTH_,DATE_,SALESREP 
ORDER BY   SALESREP)-1 AS DEL_ID INTO

SALES_INFO_SNAPSHOT_TEMP FROM SALES_INFO_SNAPSHOT_TEST
WHERE DATE_ = '10/31/2018'


This did exactly what I wanted.  A column with sequential numbers beginning with 0 and was inserted with existing data into a new table. The sequential numbers restarted at each change in year_,month_,date_,salesrep.

This allowed me to determine which of four duplicate rows per SalesRep to be deleted.  There were over a thousand rows in all for date_ = '10/31/2018' so I wanted to get this right the first time.
So I was able to determine which row to salvage (the same for each SalesRep) and send a DELETE statement.  It was determined to salvage where ID = '1'.

DELETE FROM SALES_INFO_SNAPSHOT_TEMP WHERE DATE_ = '10/31/2018' AND ID IN ('0','2','3')


Worked like a charm :)