Generate random row order in a subquery

Question

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.

I'm trying to pick a random value from lookupTable. That table has only a few rows. In the real world I want to update myTable set someColumn = ... a random lookupTable.value value such that each row in myTable is set with a random value and not such that a single random value is generated and assigned to all rows.

Answer 1

One way to achieve this is to ensure the subquery is correlated i.e. it depends in some way on the outer query. Using an AdventureWorks example:

SELECT 
    E.BusinessEntityID,
    R.DepartmentID
FROM HumanResources.Employee AS E
CROSS APPLY
(
    SELECT TOP (1)
        D.DepartmentID
    FROM HumanResources.Department AS D
    ORDER BY 
        NEWID(),
        CHECKSUM(E.BusinessEntityID, D.DepartmentID) -- Correlation
) AS R;

db<>fiddle demo

Without the correlation, SQL Server recognises that the inner query logically only needs to be run once.

The example above uses CHECKSUM(E.BusinessEntityID, D.DepartmentID) to ensure the intended correlation remains after query optimization. If we just add E.BusinessEntityID, the optimizer realizes its value is constant per iteration, removes the dependency, and we're back where we started.

The semantics of the example are also slightly different from the query in the question. It does not return NULL if lookupTable is empty.