How do I select X random rows while guaranteeing that Y certain specific rows are in the result?
random, select, sql-server, sql-server-2008
Solution
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)
Problem
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.