Random number on a per row basis

So you've tried to get a random number on a per row basis however RAND isn't evaluated on a per row basis but on a row by row basis.

However you can try the following,

 select abs(cast(newid() as binary(6)) %1000) + 1

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

If you want to change the range of values just change the 1000 to the maximum value you want.

Use this as the source of a report server report and chart the results to see the distribution

SELECT randomNumber, Count(1) countOfRandomNumber
FROM (SELECT abs(cast(newid() as binary(6)) %1000) + 1 randomNumber
        FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber

 

 

-
Published 13 June 2005 09:12 by simonsabin

Comments

15 May 2009 00:07 by Good thing about a blog

# Good thing about a blog

Pingback from  Good thing about a blog

15 May 2009 02:14 by SQL SERVER – Random Number Generator Script – SQL Query « Journey to SQL Authority with Pinal Dave

# SQL SERVER – Random Number Generator Script – SQL Query « Journey to SQL Authority with Pinal Dave

Pingback from  SQL SERVER – Random Number Generator Script – SQL Query «  Journey to SQL Authority with Pinal Dave

23 February 2010 18:43 by Script para gerar n??mero rand??mico no SQL Server « Adeilson

# Script para gerar n??mero rand??mico no SQL Server « Adeilson

Pingback from  Script para gerar n??mero rand??mico no SQL Server « Adeilson