sql - Sort data within a table -

i have table id primary key (also identity) , datetimestamp column. need update table id's sorted per timestamp values shown below. not ids present. ids correct , datetimestamps jumbled , need sorted.

database table present data -

id  datetimestamp --  ----------------------- 1   2013-08-08 14:08:43.560 2   2013-08-05 14:08:46.963 4   2013-08-06 14:08:53.247 5   2013-08-04 14:08:55.610 6   2013-08-03 14:08:58.543 8   2013-08-05 14:08:46.963 9   2013-08-06 14:08:53.247 10  2013-08-04 14:08:55.610 11  2013-08-03 14:08:58.543 

data needed -

id  datetimestamp --  ----------------------- 1   2013-08-03 14:08:58.543 2   2013-08-03 14:08:58.543 4   2013-08-04 14:08:55.610 5   2013-08-04 14:08:55.610 6   2013-08-05 14:08:46.963 8   2013-08-05 14:08:46.963 9   2013-08-06 14:08:53.247 10  2013-08-06 14:08:53.247 11  2013-08-08 14:08:43.560 

below script can create sample data -

create table #tmp_play (id int identity (1,1) primary key, datetimestamp datetime)  insert #tmp_play values (getdate()); insert #tmp_play values (getdate()-3); insert #tmp_play values (getdate()-1); insert #tmp_play values (getdate()-2); insert #tmp_play values (getdate()-4); insert #tmp_play values (getdate()-5);  delete #tmp_play id = 3  insert #tmp_play (datetimestamp)  select datetimestamp #tmp_play  delete #tmp_play id = 7 

i tried below approach, can't used because of missing ids.

with sorted  (select top 100 row_number() over(order datetimestamp) rownum, *   #tmp_play order datetimestamp) update t set t.datetimestamp = s.datetimestamp #tmp_play t join sorted s on t.id = s.rownum 

any idea how data can sorted ?

why order matter? shouldn't matter application or business id 1 has smaller/larger time value id 2. understand trying fix deemed bad data, shouldn't impact application. agree sequence number better derived doesn't offer value.

with said, solve problem @ hand, need 2 sets of sequence numbers. 1 datetimestamp , other id. can join on these 2 update rows.

;with id_order ( select *, id_seq = row_number() over(order id) #tmp_play ), dt_order ( select *, dt_seq = row_number() over(order datetimestamp asc) #tmp_play ) update set datetimestamp = dt.datetimestamp id_order inner join dt_order dt     on a.id_seq = dt.dt_seq