You know that RAND() function in SQL Server generates a random float value 0 through 1 (excluding 0 and 1). In this article, I will show you how to generate different random number for each group using RAND() Function.

First, let's us create the following dataset:

CREATE TABLE #random(no INT)
INSERT INTO #random(no)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3

If you want to generate a random value, you can use the following code:

SELECT no, RAND() AS random_number FROM #random

This code will result to:
no random_number
 1 0.370366365964781
 1 0.370366365964781
 1 0.370366365964781
 2 0.370366365964781
 3 0.370366365964781
 3 0.370366365964781

Please note that when you execute it , you may get different random number than what I got for column 2 but all will be same. What if you want to generate random number in such a way that it is reset to each column value (in this case the column is no)? Did you know that RAND() accepts a seed value as well?

If you execute the following code:

SELECT no, RAND() AS random_number,RAND(no) AS random_number_reset FROM #random

Then, it will comes to the result:
no random_number random_number_reset
 1 0.58334760467751 0.713591993212924
 1 0.58334760467751 0.713591993212924
 1 0.58334760467751 0.713591993212924
 2 0.58334760467751 0.713610626184182
 3 0.58334760467751 0.71362925915544
 3 0.58334760467751 0.71362925915544

Please note that when you execute it , you may get different random number than what I got for column2 2 and 3. If you notice the result the values of second column is same for all rows, whereas the values of third column is same withing a group (column no), and different across groups. So if you want to generate random number differently for each group, you can make use of this method.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.