How do you generate random row order in a subquery?


I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery – so as to generate a random selection per row in the outer query – using newid() yields the same result each time.

That is:

select *,     (select top 1 [value] from lookupTable where [code] = 'TEST') order by newid()) from myTable 

… yields the same lookupTable.value value on each row returned from myTable.