RANDOM QUESTIONS FROM SQL SERVER

lois waisbrooker

Total Post:70

Points:494
Posted by  lois waisbrooker
 1202  View(s)
Ratings:
Rate this:
Hi! Everyone!

I am working on a survey application. I have a sql server database holding upto 3000 survey questions. I call database for a set of 10 questions. I have to make sure 

that none of the of the questions are repeated for the longest time possible. What is the best possible approach I can take here? Write a custom randomization 

algorithm or SQL server provides some functionality.

Please help me!

Thanks in advance!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: random questions from sql server

    Hi Loos!


    SELECT question_id
    FROM questions 
    ORDER BY NEWID()
    gives you all the question ids in random order. store the list, and work your way down.

    There isn't a way to ensure the longest time between repeats without storing the order. You could make it another column on the same table...

    EDIT: a variation on Mr Jacques' idea:

    how about a column named random? Shuffle the order like so:

    update questions set random = (
        SELECT question_id
        FROM questions 
        ORDER BY NEWID()
    )
    then get each new question like this:

    select * where random = 'prev_question_id';
    Then you could shuffle the order at intervals, every night maybe.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!