random number - 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 2000 Forums

Transact-SQL (2000)

random number

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-19 : 02:54:35

in an sp how can I get a random integer number between 1 and 7

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 03:01:11

This is one of the methods

To get all numbers between 1 and 7

Select number from
(
	select 1 as number union all 
	select 2 union all
	select 3 union all
	select 4 union all
	select 5 union all
	select 6 union all
	select 7 
) T
Order by newid()

To get any one number use Top 1 number

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 03:02:47

I think this is simpler

Select abs(cast(cast(newid() as varbinary(10)) as int)%8)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-19 : 03:09:51

Slightly simpler

select abs(cast(newid() as varbinary(10)) % 8)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 03:28:40

Yes it is. Implicit conversion

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-19 : 05:07:37

or simply use rand() function

select ceiling(abs(rand()*10-7))

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-19 : 05:36:11

Hi all,

quote:
select abs(cast(newid() as varbinary(10)) % 8)

A minor point

, but to produce the requested range, I guess this should be...

select 1 + abs(cast(newid() as varbinary(10)) % 7)

quote:
select ceiling(abs(rand()*10-7))

Another minor point

, but this gives double the weighting to 1-3 - although that might not be an issue, of course.

My choice would be...

select ceiling(rand()*7)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-19 : 05:43:43

oh come on ryan... join the fun of overcomplicating this...

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 05:45:53

>>select 1 + abs(cast(newid() as varbinary(10)) % 7)

I think you added 1 to avoid 0 as the result.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources