Date: 07/25/06 (SQL Server)    Keywords: sql

Okay, here's one that's a bit beyond my SQL Server knowledge.

I need to generate a random number from 1 to n (where n is 2, 3, or 4) for a column being inserted into a table from a select from a temporary table.


INSERT INTO QA_Exam_QuestionsDetail (
  EmployeeID, ExamID,
  QuestionID, AnswerID,
  OriginalAnswerID
)
SELECT
  @EmployeeID, @examID,
  QuestionID, Round((@Upper * Rand() + 1), 0),
  B.AnswerID
FROM #tempSelectedQuestionTable A, QA_Answers B
WHERE A.QuestionID = B.QuestionID


I've removed a lot of stuff from this query, there are many other columns but those aren't important.

Yes, it's stupid that I have to use the AnswerID to key against another table, but need to at the same time randomize this ID so the display order is different for each session. No, we can't do it in the front end. It's Flash and even worse than this garbage.

Here are the problems:

1. @Upper isn't actually a variable. It has to be counted from the table for each set of values that are keyed by their QuestionID. So...there could be data like this
   
   QuestionID AnswerID
2          1
2          2
2          3
3          1
3          2
3          3
3          4

For both sets of rows, QuestionID 2 and QuestionID 3, the upper bound of the random value is different: 3 for QuestionID 2, and 4 for QuestionID 3. I guess this sounds like a nested select but I'm not sure how to write this. Usually there will be about 50 questions, so we're looking at up to 200 or so rows.
   


2. I don't think RAND() returns a different value for every row, only once for the query. What are the alternatives?

Source: http://community.livejournal.com/sqlserver/50919.html