sql server - SQL - ID to date, multiple dates -

this table:

date        sssn    myid    hours 01012013    1234            8 01012013    2345            7 01012013    3456            8 02012013    1234            5 02012013    2345            12 02012013    3456            7 

i want set myid table looks this:

date        sssn    myid    hours 01012013    1234    1       8 01012013    2345    1       7 01012013    3456    1       8 02012013    1234    2       5 02012013    2345    2       12 02012013    3456    2       7 

i looking @ this: syntax of for-loop in sql server , sql : update id; according date didn´t there.

seems easy job somehow not working desired.

i cannot use alter table identity(1,1) clear.

this when using sql server.

solved using, dense_rank() instead of row_number() suggested bluefeet.

depending on database product using, might able apply row_number() generate myid value:

;with cte (   select date, sssn, myid, hours,     row_number() over(partition sssn order date) rn   yourtable )  update cte set myid = rn; 

see sql fiddle demo.

the row_number() function available in modern rdbms.

edit #1, since can have multiple rows of sssn on each date, want use dense_rank:

;with cte (   select date, sssn, myid, hours,     dense_rank() over(order date) rn   yourtable )  update cte set myid = rn; 

see sql fiddle demo