Different rand() value for multiple rows SQL Server

Question

When I use this query, it assigns every row to the same value. Is there anyway to make this not happen?

UPDATE Team
SET GroupID = CAST(RAND() * 4 AS INT)

TO CLARIFY: This is to be used with Netbeans to assign group numbers to teams.

+----+----------+-------------+------------------+---------+
| ID |  School  | TeamCaptain |      Email       | GroupID |
+----+----------+-------------+------------------+---------+
|  1 | School 1 | John        | email@email.com  | NULL    |
|  2 | School 2 | James       | email2@email.com | NULL    |
+----+----------+-------------+------------------+---------+

SandySands method works. However, is there any way to make it so that the same number can't appear more than 4 times?

I think you can make you SQL field just auto increment and everything will work — VostanAzatyan, Jan 07 '15 at 10:51
Have a look at this http://stackoverflow.com/questions/1214748/rand-not-different-for-every-row-in-t-sql-update — Giorgos Betsos, Jan 07 '15 at 11:01
Provide table structure and you expected output in the form of tables @Christopher — Sarath Subramanian, Jan 07 '15 at 11:01

SanyTiger · Accepted Answer · 2015-01-07T11:54:26.130

0

Try this, it's working for me.

UPDATE Team
SET GroupID = CAST(RAND(CHECKSUM(NEWID()))*10000000 AS INT)
WHERE GroupID IN (SELECT GroupID FROM Team
GROUP BY GroupID
HAVING COUNT(GroupID)<4)

This code ensures random values are assigned for all GroupId's in Team table AND no GroupID would have an occurrence of more than 4

edited Jan 07 '15 at 11:54

answered Jan 07 '15 at 11:11

SanyTiger

666
1
8
23

.. You should write the logic so it produces four values rather than 1000000 values. – Gordon Linoff Jan 07 '15 at 11:30
@SandySands Yes this works, thanks. Any way to make it so that the same number can't appear more than 4 times? – Christopher Jan 07 '15 at 11:36
@SandySands I just tried this, and it affects 0 rows. – Christopher Jan 07 '15 at 12:13
I tested it again and it runs fine. Just copy the exact text as given above and try again for table Team and column GroupID – SanyTiger Jan 07 '15 at 12:19
@SandySands (0 row(s) affected) when I'm running the exact same query as you wrote. – Christopher Jan 08 '15 at 09:53

score 0 · Answer 2 · answered Jan 07 '15 at 11:30

One way to do it is:

SELECT *, ABS(CAST(NEWID() AS binary(6)) %4) + 1 randomNumber
FROM TEAM

Used in an update:

UPDATE Team
SET GroupID = ABS(CAST(NEWID() AS binary(6)) %4) + 1 randomNumber

Solution taken from:

SQL SERVER – Random Number Generator Script – SQL Query

Note: The distribution is pretty good however there are the occasional peaks.

Different rand() value for multiple rows SQL Server

2 Answers2