List:General Discussion« Previous MessageNext Message »
From:Chad Date:May 25 1999 7:43pm
Subject:least queries possible
View as plain text  
I am working on generating a quasi-random test from a bank in a
database. The question bank is structured as

id | test # | question # | question |
1	a		1		blah 
2	a		1		blah
3	a		1		blah
4	a		2		blah

id is a unique identifier for the record, test is used to determine what
test the question is for (there is an a and a b test), and the question
# identifies what question this is a cantidate for. The idea is that
there are several questions that each correspond to question #1, and one
will randomly be chosen for question #1 in the test.

What I'm trying to figure out is how to do this as efficiently as
possible. About all I can come up with now is to query the whole table
and get back all the results, then have my program implement the logic
to choose questions.

Is there any more efficient way to do this with the query? Ideally what
I would like to get back would be a random question id for each question
#, but this doesn't seem possible without subselects...

In other words, can I get back something like:

id	|	question #
2		1
6		2
8		3

where id is randomly chosen for a specific question #...
least queries possibleChad25 May