title: Pseudo Random Repeatable Sort in SQL Server (not NEWID() and not RAND())
body: <p>I would like to randomly sort a result in a repeatable fashion for purposes such as paging. For this NEWID() is too random in that the same results cannot be re-obtained. Order by Rand(seed) would be ideal as with the same seed the same random collection would result. Unfortunately, the Rand() state resets with every row, does anyone have a solution?</p>
  
  <pre><code>declare @seed as int;
  set @seed = 1000;
  
  create table temp (
  id int,
  date datetime)
  
  insert into temp (id, date) values (1,'20090119')
  insert into temp (id, date) values (2,'20090118')
  insert into temp (id, date) values (3,'20090117')
  insert into temp (id, date) values (4,'20090116')
  insert into temp (id, date) values (5,'20090115')
  insert into temp (id, date) values (6,'20090114')
  
  -- re-seeds for every item
  select *, RAND(), RAND(id+@seed) as r from temp order by r
  --1 2009-01-19 00:00:00.000 0.277720118060575   0.732224964471124
  --2 2009-01-18 00:00:00.000 0.277720118060575   0.732243597442382
  --3 2009-01-17 00:00:00.000 0.277720118060575   0.73226223041364
  --4 2009-01-16 00:00:00.000 0.277720118060575   0.732280863384898
  --5 2009-01-15 00:00:00.000 0.277720118060575   0.732299496356156
  --6 2009-01-14 00:00:00.000 0.277720118060575   0.732318129327415
  -- Note how the last column is +=~0.00002
  
  drop table temp
  
  -- interestingly this works:
  select RAND(@seed), RAND()
  --0.732206331499865 0.306382810665955
  </code></pre>
  
  <p>Note, I tried Rand(ID) but that just turns out to be sorted. Apparently Rand(n) &lt; Rand(n+1)</p>