TSQL random value from table select in every row

Question

Using the RAND() function or NEWID() to generate a random value in each column as described here you can generate random values in a row. But... the value repeats the same in every row.

enter image description here

How can you generate a random value in each row for every column? I would like to show in column B1 a random product which is also in group '102'. The same for column B2 but a different product. And the next row again two different products and so on. The result will show 2 alternative products from the same group per product (row).

My code looks something like this:

DECLARE @B1 varchar(30)
DECLARE @B2 varchar(30)
SET @B1 = (SELECT TOP 1 ItemCode FROM items WHERE items.Assortment IN (102) ORDER  BY RAND())
SET @B2 = (SELECT TOP 1 ItemCode FROM items WHERE items.Assortment IN (102) ORDER  BY NEWID())

SELECT ProdCode, ProdName, @B1 as B1, @B2 as B2
FROM Products
WHERE ProductGroup IN (102) --use IN so you can select multiple groups

Thanks!

This is how RAND() works - it is evaluated exactly once. See [this question](http://stackoverflow.com/questions/94906/how-do-i-return-random-numbers-as-a-column-in-sql-server-2005) and [this question](http://dba.stackexchange.com/questions/1625/how-can-i-assign-different-random-values-to-each-row-in-a-select-statement/) for other ideas. — Aaron Bertrand, May 08 '14 at 15:03
@Serpiton I have tried that before, it still gives the same results. — J3FFK, May 08 '14 at 15:21
@AaronBertrand thanks, [this link](http://dba.stackexchange.com/questions/1625/how-can-i-assign-different-random-values-to-each-row-in-a-select-statement/) is helpful, but it is not quite clear to me how I should apply this in my situation. Can you point me in the right direction perhaps? Thanks! — J3FFK, May 08 '14 at 15:31
I suspect that your question is more about unique IDs, but you might explore different ways of seeding the random. SELECT AddressID, RAND(AddressID) AS Random FROM SalesLT.[Address] — Steve Jackson, May 08 '14 at 15:36

score 4 · Accepted Answer · edited May 08 '14 at 16:05

4

You need to add something from the main query so the subquery gets recalculated for each row - ie:

SELECT 
    ProdCode, 
    ProdName, 
    (
        SELECT TOP 1 ItemCode 
        FROM items 
        WHERE items.Assortment IN (102) AND itemcode <> products.ProdCode
        ORDER BY NEWID(), products.ProdCode
    )
FROM Products
WHERE ProductGroup IN (102)

edited May 08 '14 at 16:05

J3FFK

664
3
14
32

answered May 08 '14 at 15:45

podiluska

50,950
7
98
104

yes it should use a subselect with reference to the main select. I tried your solutions and the result is, it gives a correct random value in row 1, but nulls in every other row. However I put itemcode <> products.ProdCode in the where in the subselect and that works. I have edited your answer so everyone can read the code that worked for this question. – J3FFK May 08 '14 at 16:03

score -3 · Answer 2 · answered May 08 '14 at 15:07

Here is a way that would work by using a while loop to loop through the rows in your table:

If you have an ID Column or something on the Products Table or insert it into a temp table to handle the calculation the below method will help.

    DECLARE @MaxRowID INT
    DECLARE @RowID INT
    DECLARE @RandomNum FLOAT

    SET @RowID = 1

    SELECT @MaxRowID = MAX(ID) FROM Products

    WHILE ( @RowID <= @MaxRowID )
        BEGIN

            SET @RandomNum = RAND()

            UPDATE Products
            SET RandomNum = @RandomNum
            WHERE ID = @RowID

            SET @RowID = @RowID + 1

        END

    SELECT * FROM #Test

    DROP TABLE #Test

I have not had many problems with it, but if you ware dealing with a big enough data set it could have an issue. — JesalynOlson, May 08 '14 at 15:12

TSQL random value from table select in every row

2 Answers2