Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select X random rows while guaranteeing that Y certain specific rows are in the result?

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.

like image 633
Chris Cudmore Avatar asked Jun 03 '12 18:06

Chris Cudmore


2 Answers

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)

like image 120
Chris Moutray Avatar answered Oct 18 '22 06:10

Chris Moutray


;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 
like image 28
Martin Smith Avatar answered Oct 18 '22 05:10

Martin Smith