October 1, 2003 at 2:12 am
I have a database of questions and another with associated answers. The questions are divided into 10 sets of three questions each. I'm trying to select one of the three questions from each set to come up with a randomised set of questions for a given assessment.
My table designs are as follows:
table tblQuestion
QN_IDint
QN_AS_ID int
QN_Nosmallint
QN_Setsmallint
QN_Texttext
QN_Score smallint
table tblAnswers
AN_IDint
AN_QN_IDint
AN_SeqNosmallint
AN_AnswerTexttext
AN_TrueFalsebit
AN_FeedbackTexttext
I've managed to get the questions returned in random question order sorted by Question Set with the following code but I only need one out of the three questions being returned in each set.
Select QN_ID, QN_Set, QN_No, newid() as SortOrder from tblQuestion
where QN_AS_ID = 2 and exists (Select top 10 QN_ID from tblQuestion)
order by QN_Set, SortOrder
Needless to say if I can't correct this then trying to add the associated answers is going to be a waste of time.
Should I be trying to do this with ASP code or can SQL achieve this. Or should I be attempting this with T-SQL, views, a combination of the above, none of the above?
Thanks for any help.
Cheers
Brett
October 1, 2003 at 5:35 am
SELECT Qn_Id, Qn_Set, Qn_No
FROM Questions q
WHERE Qn_Id =
(SELECT TOP 1 Qn_Id
FROM Questions
WHERE Qn_Set = q.Qn_Set
ORDER BY NEWID())
ORDER BY Qn_Set
--Jonathan
--Jonathan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply