Returning Rows in Random Order - Part III - Practical Applications

By Bill Graziano on 12 September 2000 | 0 Comments | 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 :)

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Eliminate characters from a string based on anothe (4 Replies)

Pivot Or not Pivot ( or UnPivot) (1 Reply)

Preparation for 70-461 Certification Exam (1 Reply)

Max Date Select not working properly (7 Replies)

effective query when there is lot of rows involved (7 Replies)

Basic SQL Help (2 Replies)

Creating a 2nd Schedule Table (1 Reply)

SQL query help- Column headers and rows frm tables (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -