How to Get Random Numbers

Question

Post reply

How to Get Random Numbers

parthi-1705

SSCrazy Eights

Points: 9277
More actions
November 26, 2009 at 10:31 pm

#96121

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

use Rand() as [your values here or reference]

example:

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

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

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[/url]

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

Thanx Dave, never too old to learn something new 🙂

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

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[/url]

carolwood Old Hand Points: 327 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

@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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

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[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Dave Ballantyne (6/27/2010)
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

Heh... yeah... hindsight is 20/20. That's a nice, short, to-the-point blog article, Dave. Keep up the good work. 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 11

a quick bump on an old post. i just needed to generate some random strings, and had this saved in my snippets. when i tested it, it didn't work, it returned the same string 100x times, and there's a comment that explicitly says there's an attempt to trick the optimizer.

the "case when Num >= 0 then 8 else 8 end,16," trick mentioned to fool the optimizer does not work on SQL2012 or SQL2014;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!