Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What would be your perfect output please? maybe I'm missing something –  gbn Mar 8 '11 at 18:48
    
I'm preparing a cursor version to make it clear –  bernd_k Mar 8 '11 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? –  gbn Mar 8 '11 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. –  bernd_k Mar 8 '11 at 19:23

1 Answer 1

up vote 6 down vote accepted

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
share|improve this answer
    
+1 That is indeed the answer that I was looking for. –  bernd_k Mar 8 '11 at 19:55
    
APPLY is SQL Server 2005 and upper –  bernd_k Mar 9 '11 at 6:37
1  
@bernd_k: yep, but it should be realistic to ignore SQL Server 2000 users in 2011... –  gbn Mar 9 '11 at 7:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.