Generating random integers - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Generating random integers

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2008-02-11 : 19:14:51

Hi -- Can someone tell me why this SELECT statement sometimes returns NULL? I'm trying to get this to always pick 0 or 1 randomly but NULL values are sometimes being returned (along with some M's and F's at other times). Any ideas?

SELECT CASE CAST(ROUND(RAND(), 0) AS BIT)
WHEN 0 THEN 'M' --Male
WHEN 1 THEN 'F' --Female
END

Thanks in advance,

Bill

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-11 : 19:33:11

no sure why but try this:

select when CAST(ROUND(RAND(), 0) AS BIT) = 0 then 'M' else 'F' end

Be One with the Optimizer
TG

billsox
Yak Posting Veteran

74 Posts

Posted - 2008-02-11 : 19:39:47

quote:
Originally posted by TG

no sure why but try this:

select when CAST(ROUND(RAND(), 0) AS BIT) = 0 then 'M' else 'F' end

Be One with the Optimizer
TG

Yeah, I know I could do that but I'm baffled as to why it's happening. If I use your solution then it's not really random since the NULL value comes up about 1/3 of the time. Therefore, 'F' would be much more likely to be returned.

What's also interesting is that if I cache the value of "CAST(ROUND(RAND(), 0) AS BIT)" in a bit variable, the NULLs never occur. But in my situation, I'd rather not have to cache the value.

Any ideas as to why?

Bill

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-11 : 19:48:22

The rand is never actually returning null. for some reason that style of CASE is causing the problem. I tried this solution for 3000 iterations and came up with a pretty even distribution of 0 and 1:

----------- -----------
0 1528
1 1469

EDIT:
Here is the code it tried:


create Table #i (i int null)

declare @i int
set @i = 1
set nocount on
while @i < 1000
begin
	insert #i (i)
	select CAST(ROUND(RAND(), 0) AS BIT) 
	set @i = @i+1
end

select i, count(*) from #i group by i

drop table #i

Be One with the Optimizer
TG

billsox
Yak Posting Veteran

74 Posts

Posted - 2008-02-11 : 19:54:18

quote:
Originally posted by TG

The rand is never actually returning null. for some reason that style of CASE is causing the problem. I tried this solution for 3000 iterations and came up with a pretty even distribution of 0 and 1:

----------- -----------
0 1528
1 1469

Be One with the Optimizer
TG

You're right, TG. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 01:47:57

or

SELECT CASE number
WHEN 0 THEN 'M' --Male
WHEN 1 THEN 'F' --Female
END
from
(select top 1 number%2 as number from master..spt_values where type='p' order by newid()) as t

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 03:07:04

SELECT CAST ABS(CHECKSUM(NEWID())) % 2
WHEN 0 THEN 'Male'
ELSE 'Female'
END AS Gender

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 04:26:15

quote:
Originally posted by Peso

SELECT ~~CAST~~ CASE ABS(CHECKSUM(NEWID())) % 2
WHEN 0 THEN 'Male'
ELSE 'Female'
END AS Gender

E 12°55'05.25"
N 56°04'39.16"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 04:28:18

Need more coffee now!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources