Please look at this code:
create table #t1(
id int identity (1,1),
val varchar(10)
);
insert into #t1 values ('a');
insert into #t1 values ('b');
insert into #t1 values ('c');
insert into #t1 values ('d');
Now, whenever you execute this
select *,
( select top 1 val from #t1 order by NEWID()) rnd
from #t1 order by 1;
you will get a result with where all rows have the same random value. e.g.
id val rnd
----------- ---------- ----------
1 a b
2 b b
3 c b
4 d b
I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.
A clever solution to this is
select t1.id, t1.val, t2.val
from #t1 t1
join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on t1.id = t2.lfd
But I simplified the query. The real query looks more like
select *,
( select top 1 val from t2 where t2.x <> t1.y order by NEWID()) rnd
from t1 order by 1;
and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of
( select top 1 val from #t1 order by NEWID()) rnd
without the use of cursors.
Edit: Wanted output:
perhaps 1 call
id val rnd
----------- ---------- ----------
1 a c
2 b c
3 c b
4 d a
and a second call
id val rnd
----------- ---------- ----------
1 a a
2 b d
3 c d
4 d b
The value for each row just should be a random value independent from the other rows
Here is the cursor version of the code:
CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));
DECLARE @id INT
DECLARE @val VARCHAR(10)
DECLARE c CURSOR FOR
SELECT id, val
FROM #t1
OPEN c
FETCH NEXT FROM c INTO @id, @val
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #res
SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd
FETCH NEXT FROM c INTO @id, @val
END
CLOSE c
DEALLOCATE c
SELECT * FROM #res