Select an arbitrary partitioned record with the specified number of records in each section

Question

We have a table (called QuestionBank) on which each question is stored:

Id Topic Hardness Position 4 1 3 4 5 1 2 1 6 1 1 2 7 1 3 3 8 1 3 4 9 2 2 1 10 2 2 2 11 2 3 3 12 3 1 1 13 3 1 1 14 3 1 2 

Each question relates to a topic and has its own firmness and position (each similar question, based on their content, has the same position on the corresponding topic).

Note that the value in the Position column does not make sense, and it simply shares similar questions in the subject.

The goal is to select N records with X-records Harness = 1 and Y Hardness = 2 and Z-records Hardness = 3, etc. randomly; with this limitation that as far as possible there are no entries with a similar topic and position returned in the result set.

For example, entries with Id = 4 and Id = 8 are similar in any topic and position, so one of them should be in the result set.

Answer 1

Here is one way.

• First, if any Topic, Position has a few questions, assign each one a random ordering of RN1 .
• Then sort, but arbitrarily inside each group. All values ​​of 1 for RN1 will obviously be ordered first before getting to 2 , which means the second question for a particular combination of Topic, Position .
• Then use the result of calculating ROW_NUMBER , broken up by Hardness , to make the final SELECT simpler.

.

 WITH T1 AS (SELECT *, Row_number() OVER (PARTITION BY Topic, Position ORDER BY Newid()) AS RN1 FROM QuestionBank), T2 AS (SELECT *, Row_number() OVER (ORDER BY RN1, Newid()) AS RN2 FROM T1), T3 AS (SELECT *, Row_number() OVER (PARTITION BY Hardness ORDER BY RN2) AS RN3 FROM T2) SELECT Id, Topic, Hardness, Position FROM T3 WHERE ( Hardness = 1 AND RN3 <= 3 ) OR ( Hardness = 2 AND RN3 <= 2 ) OR ( Hardness = 3 AND RN3 <= 2 ) ORDER BY Topic, Position, Hardness 

If you select only a small part of the table each time, there are likely to be much more efficient ways.

Answer 2

You can select a WHILE loop to select a question at a time.

To select a random question:

• select all Hardness 1 entries in the temporary table
• Use rand () to select one of them.
• Check if there was a previously selected quest with the same topic / position, if this was not the case for the selected questions and remove it from the selection made in paragraph 1.
• Follow steps 2 through 3 until N records for hardness are indicated.
• Follow steps 1 to 4 for other hardness values.