Returning Rows in Random Order - Part III - Practical Applications

By Bill Graziano on 12 September 2000 | Tags: Randomness

Kaushik writes "Hello, I have table Question,ProbableAnswer. I want to pick up data from this tables in a random order. I am using Sql 7.0 Windows NT 4. This is basically to create a online test. Depending on the subject selected i want to select questions in a random order and insert it into a table and then for those question i want to pick up 4 choices ie: 1 correct, 3 wrong in a random order and then display it in a random manner. Thank you in advance." I really like this question :)

---

Note: The approach in this article has been replaced by Using NEWID to Randomly Sort Records if you're using Windows 2000 or higher.

---

I was just about to post this question in the Forums and than I realized that I really like this question. Sean recently wrote an article about returning rows in a random order. We'll use this as the basis for the solution so please take a second and review it. This is going to get a little complicated so stick with me.

The first step is to select the questions in a random order. You can follow the steps in the previous article to accomplish this. The fun part starts when you want to randomize the possible answers. I'm assuming that you have one correct answer and at least three wrong answers for each question.

First build a temporary table that will hold the four possible answers. Insert the one correct answer into this table. Next, randomize all the possible wrong answers for this question and put the first three into this table. You can do this by following Sean's approach and using a SELECT TOP 3 statment. This will give you four answers in the table with the first one always the correct answer.

Finally, you need to randomize those four answers using the method from Sean's article. That will return the four answers in a random order. You are probably best to put all this in a stored procedure and pass it a QuestionID as a parameter. It will do all it's randomizing and just return the final result set. Have fun with this one :)