No... not from a sub-query. At least I haven't figured out a way to do it with a TOP 1/ORDER BY like that.

You could do it using a CASE function based on ABS(CHECKSUM(NEWID()))%3 but that will actually come up with 4 values because, since NEWID() isn't deterministic, the CASE function will recalculate each and every WHEN even if you use CASE ABS(CHECKSUM(NEWID()))%3. You could then change the formula to ABS(CHECKSUM(NEWID()))%2 and use ELSE but that will give you an uneven distribution.

So, the only thing to do is to gen the numbers that control the color separately and then CASE that number. Here's one way of doing that in a single query.

DROP TABLE #MyHead;

WITH

cteRandom AS

( --=== Generate the random number first

SELECT TOP (1000)

ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

RandomColor# = ABS(CHECKSUM(NEWID()))%3

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

) --=== Now, pick the color according to the generated number

SELECT ID,

RandomColor = CASE RandomColor#

WHEN 1 THEN 'Red'

WHEN 2 THEN 'Green'

ELSE 'Yellow'

END

INTO #MyHead

FROM cteRandom

;

--===== Show the distribution

SELECT RandomColor, COUNT(*)

FROM #MyHead

GROUP BY RandomColor

ORDER BY RandomColor