SQL Server Performance

Generate Random Number SERIES from 1 to 30 numbers with another column having 1 to 30 numbers in ascending order

Discussion in 'SQL Server 2005 General DBA Questions' started by bsethi24, Mar 31, 2010.

  1. bsethi24 New Member

    Dear All,
    Hi !
    I require a MSSQL query to generate Random Number SERIES from 1 to 30 numbers (not only a single random number but a series) in a column with another column having 1 to 30 numbers in ascending order.
    I have a query to generate random number series but, having problem to generate this random number series with another column having 1 to 30 numbers in ascending order.
    declare @MinNumber int
    declare @MaxNumber int
    set @MinNumber = 1
    set @MaxNumber = 30
    create table tempseries
    (
    NumberS int
    )
    DECLARE @No int
    set @no = @MinNumber

    while @no <= @MaxNumber
    begin
    insert into tempseries (NumberS) values (@No)
    set @no = @no+1
    end
    select NUMBERS numbers from tempseries order by newid()
    Kindly guide me.
    Thanks
  2. dhamu294 New Member

    Hope this what you want. Try this one and get back.

    select * from (
    select top 10 NumberS from tempseries order by newid())a order by 1
  3. Madhivanan Moderator

    Can you post some sample data with expected result?
  4. Madhivanan Moderator

    [quote user="dhamu294"]
    Hope this what you want. Try this one and get back.

    select * from (
    select top 10 NumberS from tempseries order by newid())a order by 1
    [/quote]
    This is equivalent to
    select top 10 NumberS from tempseries order by 1
  5. cwilson310 New Member

    I got different results for the two selects above.
  6. cwilson310 New Member

    ok, this gets you part of the way there. If you want to store the new sequential numbers as a column, I leave that up to you.select
    NumberS, seqnumfrom
    (select top 30 NumberS, ROW_NUMBER() OVER (ORDER BY newid()) as seqnumfrom
    tempseries) tmporder
    by seqnum
  7. dhamu294 New Member

    Hi madhivanan,
    each time you execute the above query you will get randaom 10 numbers between 1 to 30 which is ordered in asc. try executing it. hope that is what he want.
  8. Madhivanan Moderator

    [quote user="dhamu294"]
    Hi madhivanan,
    each time you execute the above query you will get randaom 10 numbers between 1 to 30 which is ordered in asc. try executing it. hope that is what he want.
    [/quote]
    Yes. I forgot to note that you already used newid()
  9. Adriaan New Member

    IMHO, a sequence of numbers 1 though 30 in itself is as random as it could ever need to be. The requirement appears to be to have a non-sequential series of incrementing values.
    Duh.
  10. mberg New Member

    drop table tempseries
    create table tempseries ( NumberS int, SeqNo int )
    DECLARE @Random INT;
    DECLARE @Upper INT;
    DECLARE @Lower INT;
    DECLARE @Count INT;
    DECLARE @Present INT;
    SET @Lower = 1 ---- The lowest random number
    SET @Upper = 31 ---- The highest random number plus 1
    SET @Count = 1
    WHILE @Count < @Upper
    BEGIN
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SELECT @Present =(Select NumberS FROM tempseries WHERE NumberS = @Random)
    IF @Present is null
    BEGIN
    INSERT INTO tempseries (NumberS, SeqNo) VALUES (@Random, @Count)
    SET @Count = @Count + 1
    END
    END
    select * from tempseries ORDER BY NUMBERS
    select * from tempseries ORDER BY SeqNo

Share This Page