Select Stored Number of Random Records without Using Cursors

[waddies]

I hope someone can help,

tblMatrix
QuestionTypeID QuestionGradeID Quantity
3546 1261 2
3548 1262 3
3548 1263 1

tblQuestions
QuestionID QuestionTypeID QuestionGrade
4523 3546 1261
4524 3546 1261
4525 3546 1261
4527 3546 1261

4555 3546 1262

4528 3548 1262
4529 3548 1262
4530 3548 1262
4531 3548 1262
4532 3548 1262
4533 3548 1263
4533 3548 1263
4533 3548 1263

I have two tables namely tblQuestions & tblMatrix (see above).
The tblQuestions table stores numerous questions of different types and grades.

The tblMatrix table stores records referring to combinations of QuestionTypes & Grades, along with a quantity value.

What I am trying to do is select a number of random records from the tblQuestions table, the total number of records being equal to the quantity value in the tblMatrix table

i.e. Looking at table tblMatrix,
1.for QuestionTypeID = 3546 & QUestionGradeID = 1261 I'd like to retrieve 2 random records from the possible 4 records in tblQuestions that have the same question & grade values.

2.for QuestionTypeID = 3548 & QUestionGradeID = 1262 I'd like to retrieve 3 random records from the possible 5 records in tblQuestions that have the same question & grade values.

I've succeeded in doing this using a cursor, however i wondered if there was a better way using a select statement.


Regards

 

[JamesLean]

Something like this?

DECLARE @qtype int,
  @qgrade int,
  @num int

SELECT @qtype = 3546,
  @qgrade = 1261

SELECT @num = quantity
FROM tblMatrix
WHERE QuestionTypeID = @qtype
  AND QuestionGradeID = @qgrade

SET ROWCOUNT @num

SELECT * FROM tblQuestions
WHERE QuestionTypeID = @qtype
  AND QuestionGrade = @qgrade
ORDER BY NEWID()

SET ROWCOUNT 0

--James

 

[waddies]

Thanks James,

That works.
However it only works when you specify the values for @qtype & @qgrade.

What I'd like is something which would process all the records in the Matrix table at the same time.

Would a possible answer be to put your code in a user defined function and call it from a select statement which returns the matrix records?

i.e. select dbo.functionname(qtype,qgrade)
from tblMatrix

If so would this method be more efficient than using a cursor?

 

[JamesLean]

Hmm. I think this might be one of those rare occasions when a cursor is necessary. It might be possible to do what you want if you didn't need random questions but I guess that's the essence of the problem.

--James

 

[waddies]

Thanks anyway James.