12

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
Improve this question
4
  • What would be your perfect output please? maybe I'm missing something Commented Mar 8, 2011 at 18:48
  • I'm preparing a cursor version to make it clear Commented Mar 8, 2011 at 19:08
  • So rnd and val are always different in every row? If it was "random", then occasionally they would the same. Also, in your 2 calls mentioned does it matter that rnd does not have all values on the column? Commented Mar 8, 2011 at 19:17
  • It is used to generate a small to medium random demonstration from a big pool of real data. Yes repletions are allowed. Commented Mar 8, 2011 at 19:23

1 Answer 1

Reset to default
11

A subquery is evaluated once if possible. I can't recall what the "feature" is called (folding?) sorry.

The same applies to GETDATE and RAND functions. NEWID is evaluated row by row because it in intrinsically a random value and should never generate the same value twice.

The usual techniques are to use use NEWID as input to CHECKSUM or as a seed to RAND

For random values per row:

SELECT co1l, col2, ABS(CHECKSUM(NEWID())) AS Random1, RAND(CHECKSUM(NEWID())) AS Random2 FROM MyTable

If you want random order:

SELECT co1l, col2 FROM MyTable ORDER BY NEWID()

If you want random order with a row order too. ActualOrder order here is preserved regardless of the order of the resultset

SELECT id, val, ROWNUMBER() OVER (ORDER BY id) AS id FROM #t1 ORDER BY NEWID()

Edit:

In this case, we can state the requirement as:

  1. return any random value from the set for each row in the set
  2. the random value will be different from the actual value in any row

This is different to what I offered above which simply re-orders rows in various ways

So, I'd consider CROSS APPLY. The WHERE clause force row by row evaluation and avoids the "folding" issue and ensures that val and rnd are always different. CROSS APPLY can scale quite well too

SELECT id, val, R.rnd FROM #t1 t1 CROSS APPLY (SELECT TOP 1 val as rnd FROM #t1 t2 WHERE t1.val <> t2.val ORDER BY NEWID()) R ORDER BY id
Improve this answer
2
  • APPLY is SQL Server 2005 and upper Commented Mar 9, 2011 at 6:37
  • 1
    @bernd_k: yep, but it should be realistic to ignore SQL Server 2000 users in 2011... Commented Mar 9, 2011 at 7:12

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.