sql server 2016 – 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.