Pick a random value from a set T-SQL

Question

Possible Duplicate:
Simple update statement so that all rows are assigned a different value

Is there a way to pick a random value (like a color) from a subquery? here is what I tried but getting the same value on every run:

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 am getting different colors:

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

You need to make the subquery correlated so that it is re-evaluated for each row. `WHERE COALESCE(ac1.object_id, ac2.object_id, ac1.column_id, ac2.column_id) IS NOT NULL ORDER BY NEWID()` seems to do it for me. — Martin Smith, Dec 17 '12 at 10:58

t-clausen.dk · Accepted Answer · 2013-05-29T09:07:49.003

3

Something like this perhaps

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

edited May 29 '13 at 09:07

answered Dec 17 '12 at 08:00

t-clausen.dk

43,517
12
59
92

For me on 2012 that just gives a repeating pattern. e.g. `RGYRGYRGYRGY` works on 2008 though. – Martin Smith Dec 17 '12 at 11:01
@MartinSmith interesting, it makes no sense. Seems to be a problem with the logic in 2012. Going to test it when i get access to 2012 – t-clausen.dk Dec 19 '12 at 11:35
@MartinSmith you are absolutely right, changed my script to work for mssql 2012 as well – t-clausen.dk May 29 '13 at 09:09

Pick a random value from a set T-SQL

1 Answers1