How to Get Random Numbers

  • 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

  • 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

  • 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]

  • 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

  • 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]

  • 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

  • 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)

  • 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)

  • @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 (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)

  • 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!

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

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