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 :)

Related Articles

Using NEWID to Randomly Sort Records (16 April 2002)

Another Random Record Selection Method (6 January 2002)

Multiple Random Records w/Single Select (4 October 2000)

Returning a Single Random Row (27 September 2000)

Returning Rows in Random Order - Part II - Revenge of the Randomizer (11 September 2000)

Other Recent Forum Posts

Separate column in to multiple column (8h)

Why these two statment not give same columns although no different? (8h)

Convert While loop to CTE (13h)

Multiple ordered aggregate functions in the same scope have mutually incompatible orderings (14h)

Beginner question on how to use a user defined function (16h)

Building dynamic columns not get last columns complete why? (2d)

Display the latest value (eg mobile no)from the active record of customers (2d)

Get data from remote server with only read access and save to local server (2d)

- Advertisement -