CODEWITHSUNDEEP
sqlt-sqlrandom
Faiz
Faiz

Reputation: 283

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

Upvotes: 0

Views: 111

Answers (2)

Paulo Amaral
Paulo Amaral

Reputation: 863

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(CHECKSUM(NEWID())) 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

Upvotes: -1

D Stanley
D Stanley

Reputation: 152596

No - The probability distribution should be uniform, meaning you have the same chance of getting 0.2 as 0.5, but there's no guarantee that a specified set of "random" numbers will be uniform.

Upvotes: 2

Related Questions