Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Get Random Numbers Expand / Collapse
Author
Message
Posted Thursday, November 26, 2009 10:31 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Hi

I need to get all 600 numbers in 900 there should not repeat of same Number more than three times
That is all 600 numbers should come in a random manner

SET NOCOUNT ON
DECLARE @fno INT ,@tno INT ,@I INT,@cnt INT SET @cnt=0
DECLARE @Temp TABLE(Value INT)
DECLARE @Temp1 TABLE(cnt INT,Value INT)
SET @fno=1
SET @tno=600
SET @I=1
WHILE @I<=900
BEGIN
INSERT INTO @Temp
Select Round(((@tno- @fno) * Rand() + @fno), 0)
SET @I=@I+1
END
SELECT Count(*),Value FROM @Temp GROUP BY Value

Some times it comes in 450-500 range only
I used Goto to regenerate the same but it takes long time.
but goto works fine for small range 60-90 numbers
is there any other way is there to take those numbers randomly or simplify my Query

Thanks
Parthi


Thanks
Parthi
Post #825452
Posted Thursday, November 26, 2009 11:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871, Visits: 2,692
use Rand() as [your values here or reference]

example:

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #825456
Posted Friday, November 27, 2009 1:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227
The general consensus it that abs(checksum(newid()))%100 is the bettet method to generate random numbers.

Heres an example from my blog
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx




Clear Sky SQL
My Blog
Kent user group
Post #825480
Posted Friday, November 27, 2009 1:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871, Visits: 2,692
Thanx Dave, never too old to learn something new

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #825481
Posted Friday, November 27, 2009 1:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227
To answer the question more fully,
Something like this...


with cteRandoms
as(

select top 10000 abs(checksum(NewId()))%600 as Random , ROW_NUMBER() over (order by (select null)) as RowN
from syscolumns a cross join syscolumns b
),
ctefilter
as
(
select * ,row_number() over (partition by Random order by RowN ) Rowfilter from cteRandoms
)
select top 300 Random
from ctefilter
where Rowfilter < 3
order by RowN


@Henrico, Its always a good day when you learn something new




Clear Sky SQL
My Blog
Kent user group
Post #825485
Posted Saturday, June 26, 2010 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 27, 2010 10:39 PM
Points: 9, Visits: 7
Hello Friends.......

Generating random numbers is simple. Provided as part of the java.util package, the Random class makes it easy to generate numbers. Start by creating an instance of the Random class

// Create an instance of the random class
Random r = new Random();

Now, you must request a number from the generator. Supposing we wanted an integer number, we'd call the nextInt() method.

// Get an integer
int number = r.nextInt();

Thanks


web development
Post #943419
Posted Saturday, June 26, 2010 7:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800
carolwood (6/26/2010)
Hello Friends.......

Generating random numbers is simple. Provided as part of the java.util package, the Random class makes it easy to generate numbers. Start by creating an instance of the Random class

// Create an instance of the random class
Random r = new Random();

Now, you must request a number from the generator. Supposing we wanted an integer number, we'd call the nextInt() method.

// Get an integer
int number = r.nextInt();

Thanks


Uh huh... it's also an easy thing to do in VB, C, etc, etc as well as T-SQL. The original question also asked for a certain quantity of random numbers as well as having all the generated random numbers be unique. Do you have Java code for that?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #943451
Posted Saturday, June 26, 2010 8:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800
Dave Ballantyne (11/27/2009)
The general consensus it that abs(checksum(newid()))%100 is the bettet method to generate random numbers.

Heres an example from my blog
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx


Nicely done, Dave. Here's a minor modification using your code from your article just to make the callout a bit simpler... there's no difference in performance at all...

 select Random
from numbers
cross apply GetVariableLengthRandomCode(ISNULL(8,num),16,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #943517
Posted Saturday, June 26, 2010 8:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800
@Parthi,

Are you all set now?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #943518
Posted Sunday, June 27, 2010 1:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227
Thanks Jeff , hindsight on the isnull issue

http://beyondrelational.com/blogs/dave_ballantyne/archive/2010/05/19/null-or-coalesce-what-s-the-difference.aspx





Clear Sky SQL
My Blog
Kent user group
Post #943530
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse