Return to Returning Rows in Random Order - Part III - Practical Applications ## Returning Rows in Random Order - Part III - Practical ApplicationsWritten by Kaushik writes
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 :) |