title: Sort data within a table
body: <p>I have a table with ID as a primary key (also identity) and a datetimestamp column.
  I need to update the table with ID's sorted per the timestamp values as shown below.
  Not all the ids are present. The IDs are correct and the datetimestamps are jumbled up and need to be sorted.</p>
  
  <p>Database Table with present data as - </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Data needed as -</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Below is script which can create the sample data -</p>
  
  <pre><code>create table #tmp_play
  (id int identity (1,1) primary key, datetimestamp datetime)
  
  insert into #tmp_play values (getdate());
  insert into #tmp_play values (getdate()-3);
  insert into #tmp_play values (getdate()-1);
  insert into #tmp_play values (getdate()-2);
  insert into #tmp_play values (getdate()-4);
  insert into #tmp_play values (getdate()-5);
  
  delete from #tmp_play where id = 3
  
  insert into #tmp_play (datetimestamp) 
  select datetimestamp from #tmp_play
  
  delete from #tmp_play where id = 7
  </code></pre>
  
  <p>I tried with the below approach, but this can't be used because of the missing IDs.</p>
  
  <pre><code>with sorted as 
  (select top 100 ROW_NUMBER() OVER(ORDER BY datetimestamp) as RowNum, * 
   from #tmp_play order by datetimestamp)
  update t
  set t.datetimestamp = s.datetimestamp
  from #tmp_play t
  join sorted s on t.id = s.RowNum
  </code></pre>
  
  <p>Any idea how this data can be sorted ?</p>