sql server - SQL Update sequence data based upon date field -

i attempting update table contains deed information. property id, sale sequence, , deed date. program generates sale sequence data sequentially regardless of deed date or prior deed information property in question.

[property_id]   [sale_number]   [sale_deed_date]         1               1            01/15/1990         1               2            06/25/1970         1               3            08/12/1930   

what accomplish re-sequence sale_number data in chronological order. similar this:

[property_id]   [sale_number]   [sale_deed_date]         1                1             08/12/1930         1                2             06/25/1970         1                3             01/15/1990   

any appreciated.

you can grabbing correct order in cte:

;with cte (select property_id, sales_number, sales_deed_date, rn = row_number() on (partition property_id order sales_deed_date) tablename)  update t set t.sales_number = cte.rn tablename t inner join cte on t.property_id = cte.property_id , t.sales_deed_date = cte.sales_deed_date