Assume a table structure:
Create Table Question
{
ID int pk,
Category varchar
Stem varchar,
AnswerA varchar,
...
AnswerD varchar,
Correct char,
isMandatory bit
}
For a given category, there are approximately 50 questions. There can be 1-10 mandatory questions.
I need to select all mandatory questions, and then enough other questions at random to make a question set of 20 questions.
Ok how about this
select top 20 * from question
where category = @category
order by isMandatory desc, newid()
See accepted answer for reasoning behind newid() Random record from a database table (T-SQL)
;WITH T
AS (SELECT *,
ROW_NUMBER()
OVER (PARTITION BY Category
ORDER BY isMandatory DESC, CRYPT_GEN_RANDOM(4)) RN
FROM Question)
SELECT *
FROM T
WHERE RN < = 20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With