Choose a random value from the T-SQL set

Question

Possible duplicate:
A simple update instruction, so that all rows are given a different value

Is there a way to select a random value (e.g. color) from a subquery? here is what i tried but got the same value every time I started:

SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,--Sequential number from 1 to .. (SELECT TOP 1 color FROM ( VALUES (0, 'Red'), (1, 'Green'), (2, 'Yellow') ) colors(id, color) ORDER BY NEWID() ) AS RandomColor FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 

but if I run this piece alone, I get different colors:

 SELECT TOP 1 color FROM ( VALUES (0, 'Red'), (1, 'Green'), (2, 'Yellow') ) colors(id, color) ORDER BY NEWID() 

Answer 1

Something like this is possible

 select ac1.ID, r.color from (SELECT TOP (100) -- changed because it didn't work in mssql 2012 --ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID, ROW_NUMBER() OVER (ORDER BY newid()) as ID, ROW_NUMBER() OVER (ORDER BY newid()) as dummy FROM sys.all_columns order by id ) ac1 cross apply (SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) where id = dummy % 3 ) r