CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
JamshaidRiaz
JamshaidRiaz

Reputation: 111

Returning random rows that are evenly spread over a grouping

I am creating some Examination Software where I have to pick random questions for each student, with a specific number of questions from each Subject's Chapter.

So for example, the Maths subject is divided into 12 Chapters and I want 50 random questions with at least 3 questions from each chapter.

I have the following query so far which is giving me 50 random questions. 

SELECT TOP 50 Q.ID, Q.Subject_name, Q.Chapter 
FROM  Questions Q 
WHERE 50 >= RAND() * (SELECT MAX(25) FROM Questions Q)

Upvotes: 0

Views: 140

Answers (1)

Tanner
Tanner

Reputation: 22733

You can use ROW_NUMBER to generate a sequential number for each question per chapter. On top of this, to get random rows you can generate a NEWID for each row that can be used to order the rows:

SELECT TOP 50 Q.ID, Q.Chapter , 
       ROW_NUMBER() OVER(PARTITION BY Chapter ORDER BY NEWID()) AS Row
FROM questions Q
ORDER BY Row

Each time this is run, you will get 50 random rows, that are spread over the chapters evenly.

Working SQL Fiddle

Upvotes: 3

Related Questions