Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: returning a recordset and randomising the results

    Hi,
    I have 20 questions in my SQL Server database. I now want to pull out 10 of the 20 in a random order for a test I want to give people. It must have a different set of questions so they cannot cheat.
    How is it best to pull out a random selection from the database?

    Im building it as part of an ASP.net solution.

    Andy

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Depends on the OS you have on the server but try

    select top 10 *
    from questions
    order by newid()

  3. #3
    Join Date
    Aug 2003
    Posts
    51
    One thing you could do is add a column (if not there already) as an identity column that starts at one. This will assign numbers to each of the records. You could then use the RAND function (see books online for sample). Using the RAND, you could then loop through and pull the records you are looking for. i.e.
    DECLARE @counter smallint
    Declare @rnd smallint
    SET @counter = 1
    WHILE @counter < 5
    BEGIN
    SELECT @rnd = Convert(smallint,RAND(@counter)* 20)
    SET NOCOUNT ON
    SET @counter = @counter + 1
    SET NOCOUNT OFF
    Select * from mytbl where id=@rnd
    END
    GO

    The only other thing you may have to account for is the fact that you would have the possibilty of duplicate values. That being the case, you would have to keep track of the items you already selected, then keep picking until you don't select the same message again.

    Rich

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem with the built-in RAND function is that it is deterministic, and even worse when sequential values are supplied as seeds then sequential random values are returned. About as close to useless as a randomizer can get.

    Here is a workaround. Set a seed value based on the current system time, combine it with an integer id from the table (sequential or not), then strip off the leftmost digits (using the modulo operator) until you get to a truly random portion and sort by the result:
    -------------------------------------------------------------
    declare @seed real
    set @seed = 100*(SELECT RAND((DATEPART(mm, GETDATE()) * 100000) + (DATEPART(ss, GETDATE()) * 1000) + DATEPART(ms, GETDATE())))

    select *
    from [YOUR_TABLE]
    order by cast(rand(@seed+[YOUR_ID_VALUE])*10000000 as int) % 1000
    -------------------------------------------------------------
    This will return the data in 1 of 1000 possible random orders. Note that I used code from books online to get the seed value, but you could use something with even smaller increments if you want.

    blindman

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    STOP THE PRESSES!

    This simple idea is from Microsofts website, and appears to be much more reliably random (is that an oxymoron) than the RAND function method:

    ----------------------------------------
    select *
    from [YOUR_TABLE]
    order by newid()
    ----------------------------------------

    blindman

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry Nigelrivett! Just saw your previous post....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •