Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to Get Random Numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 26, 2009 10:31 PM
 Mr 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 timesThat is all 600 numbers should come in a random manner SET NOCOUNT ONDECLARE @fno INT ,@tno INT ,@I INT,@cnt INT SET @cnt=0DECLARE @Temp TABLE(Value INT)DECLARE @Temp1 TABLE(cnt INT,Value INT)SET @fno=1SET @tno=600SET @I=1 WHILE @I<=900BEGIN INSERT INTO @Temp Select Round(((@tno- @fno) * Rand() + @fno), 0)SET @I=@I+1END SELECT Count(*),Value FROM @Temp GROUP BY ValueSome 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 QueryThanks Parthi ThanksParthi
Post #825452
 Posted Thursday, November 26, 2009 11:14 PM
 SSCommitted 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 1Divide by zero error encountered.
Post #825456
 Posted Friday, November 27, 2009 1:39 AM
 SSCommitted 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 bloghttp://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspx
Post #825480
 Posted Friday, November 27, 2009 1:41 AM
 SSCommitted 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 1Divide by zero error encountered.
Post #825481
 Posted Friday, November 27, 2009 1:56 AM
 SSCommitted 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 cteRandomsas(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),ctefilteras(select * ,row_number() over (partition by Random order by RowN ) Rowfilter from cteRandoms)select top 300 Random from ctefilter where Rowfilter < 3order by RowN`@Henrico, Its always a good day when you learn something new
Post #825485
 Posted Saturday, June 26, 2010 12:26 AM
 Forum 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 classRandom 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 integerint number = r.nextInt();Thanks web development
Post #943419
 Posted Saturday, June 26, 2010 7:55 AM
 SSC-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 classRandom 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 integerint number = r.nextInt();ThanksUh 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 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 bloghttp://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/a-faster-tsql-random-length-random-string-generator.aspxNicely 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 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 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
Post #943530

 Permissions