Using subquery to pull random value reveals same value every time

I want to add a random value to a column in a query using T-SQL for Microsoft SQL Server 2008 R2. To do this, I use a subquery and pull a random record. Every time I run the query, it pulls a new random record, but the value for every row is identical.

How can I get a new random value for each row?

Sample query:

SELECT column1,
    (SELECT TOP 1 column1 as c2 FROM Table2 ORDER BY NewID())
FROM Table1

Whereas table2 looks like

column1
value1
value2
value3

It always returns the same value for column2, instead of a new random record for each table.

First run:

column1, c2
1, value1
2, value1
3, value1

Second run:

column1, c2
1, value2
2, value2
3, value2

How can I get c2 (the subquery) to pull a new random record for each row?

Ideal scenario:

column1, c2
1, value2
2, value1
3, value2
12
задан Lucas 1 April 2012 в 02:52
поделиться