Faiz Faiz - 5 months ago 15
SQL Question

How does the rand() operator work?

Quickly wanted to ask that does the RAND() operator distribute the values in equal numbers? e.g. If I have 100 rows in a table and a column named NumberOfItems and I want to assign the values 1-10 to them such that it is divided equally between the 100 records (10 records will have 1, 10 will have 2 ......)

The statement I have is:

select CAST(RAND(CHECKSUM(NEWID())) * 10 as INT)+1

Answer

I don't have a SQL Server here to test but this will probably work for you.

SELECT
        ((ROW_NUMBER() OVER(ORDER BY T1.rand_col)) % 10) + 1 as value_col,
        T1.col1,
        T1.col2,
        T1.col3

FROM (
        SELECT col1,
               col2,
               col3,
               RAND() as rand_col
        FROM   your_table
      ) T1

ORDER BY T1.rand_col

What it does is:

  • The subselect T1 is your original table (which I called your_table), with an additional random column rand_col
  • Sort values by this random column in ORDER BY T1.rand_col
  • Give rows sequential numbers (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, ... ) by doing ROW_NUMBER() OVER(ORDER BY T1.rand_col)
  • Limit that sequential range of numbers to 0-9 by doing % 10
  • Sum 1 to change the range from 0-9 to 1-10 `(...) + 1

Some references