How to select X random strings, ensuring that some specific strings are obtained as a result?

Question

Suppose the table structure:

Create Table Question { ID int pk, Category varchar Stem varchar, AnswerA varchar, ... AnswerD varchar, Correct char, isMandatory bit } 

There are about 50 questions for this category. There may be 1-10 required questions.

I need to select all the required questions, and then enough other questions at random to ask a question of 20 questions.

Answer 1

Ok how about this

 select top 20 * from question where category = @category order by isMandatory desc, newid() 

See accepted answer for argument behind newid () Random entry from database table (T-SQL)

Answer 2

 ;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 

Answer 3

  Declare @number_of_nonmandat INT Select @number_of_nonmandat=count(1) FROM Question where isMandatory =1 SET @number_of_nonmandat=20-number_of_nonmandat; IF(@number_of_nonmandat>0) BEGIN Select * FROM Question where isMandatory =1 UNION SELECT TOP (@number_of_nonmandat) * FROM Question where isMandatory<>1 ORDER BY newID() END ELSE BEGIN Select top 20 * FROM Question where isMandatory =1 END