SQL Server Performance

Generate Unique Random Numbers between given range of numbers

Discussion in 'SQL Server 2005 General DBA Questions' started by bsethi24, Jun 8, 2009.

  1. bsethi24 New Member

    Hi ! I am in a need of generating Unique Random Numbers between given Number Range. For Example, a user provides a Number Range between 1 and 50. So, I need to generate a series of Unique Numbers from 1 to 50 in RANDOM ORDER. How can I do the same in MS-SQL?
    Kindly guide me.
    bsethi24, Jun 8, 2009
    #2

  2. Adriaan New Member

    If the range is 1 to 50, there isn't a lot of randomness to the number.
    Please do a search in Books Online for "random".
    Adriaan, Jun 8, 2009
    #3

  3. bsethi24 New Member

    Thanks for reply.
    But, I need to generate a series of Unique Numbers from 1 to 50 in RANDOM ORDER. How can I do the same in MS-SQL?
    Kindly guide me.
    bsethi24, Jun 8, 2009
    #4

  4. FrankKalis Moderator

    A series of unique number is certainly different from a random number.
    One way could be to SELECT TOP (n) number FROM a number table ORDER BY NEWID() WHERE number BETWEEN lower_bound AND upper_bound;
    FrankKalis, Jun 8, 2009
    #5

  5. Madhivanan Moderator

    declare @from_no int, @to_no int
    select
    @from_no =1, @to_no=50select
    num from(
    select row_number() over (order by s1.name) as num from sysobjects s1 cross join sysobjects s2)
    as twhere
    num>=@from_no and num<=@to_no order
    by newid()
    Madhivanan, Jun 8, 2009
    #6

  6. Wild Bill New Member

    HiHere interesting articlehttp://www.sqlservercentral.com/articles/Random+Numbers/66718/
    Wild Bill, Jun 9, 2009
    #7

Share This Page