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.
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