rand() for each row of a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

rand() for each row of a table

Author

Topic

Junior Sqler
Starting Member

18 Posts

Posted - 2013-07-04 : 07:15:20

Hello!
I have a table with 3 columns:
1.Invoice Number 2. Rating and 3.Checking

Checking is 0 or 1 depending on Rating.

I wrote the following query.

select a.Invoicenumber,a.rating,
(case when a.Rating<=3 then (case when rand() <=0.05 then 1 else 0 end)
when a.Rating>3 and a.Rating<=4 then (case when rand() <= 0.1 then 1 else 0 end)
when a.Rating>4 and a.Rating<=5 then (case when rand()<=0.2 then 1 else 0 end)
else 1 end) as checking
from #temp2

My question is every time i run it rand() takes only one value?
How could i do it so rand() is different for each row of my table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 07:47:21

rand gives single value for each execution.

try

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Junior Sqler
Starting Member

18 Posts

Posted - 2013-07-04 : 08:02:38

ook..

I have two more questions:

1. if i want my number to be decimal from 0.00 to 1.00 what should i do

2. I guess in my given query i should replace every rand() with (select top 1 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2)
right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 01:58:20

quote:
Originally posted by Junior Sqler

ook..

I have two more questions:

1. if i want my number to be decimal from 0.00 to 1.00 what should i do

2. I guess in my given query i should replace every rand() with (select top 1 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2)
right?

1.You can generate a 3 didgt random number and divide by 100
2. Yep..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Junior Sqler
Starting Member

18 Posts

Posted - 2013-07-05 : 03:04:18

thaank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources