Selecting multiple sets of rows with a single sql query

Question

I have a table of questions set out like so..

id | question | answer | syllabus | difficulty

I want to create an SQL statement that selects 5 questions at random for each of the distinct syllabuses when the difficulty is easy.

So if there are 4 syllabuses I would have 20 questions.

I was thinking something like this...

   SELECT 
    * 
FROM 
    questions 
WHERE 
    difficulty='easy' 
AND 
    syllabus 
IN 
(
    SELECT DISTINCT 
        syllabus 
    FROM 
        questions 
    WHERE 
        difficulty='easy'
) 
LIMIT 
(5*
    (
    SELECT 
        COUNT(DISTINCT syllabus) 
    FROM 
        questions 
    WHERE 
        difficulty='easy'
    )

But this doesn't return 5 from each of the distinct syllabuses only the correct number of questions from any syllabus.

Added the [greatest-n-per-group] tag. See similar question, under the **Related** header, on the right. — ypercubeᵀᴹ, Oct 24 '11 at 15:44
would be a better data model if you had a separate table with syllabus in it — Trevor North, Oct 24 '11 at 15:44
do you have any id's for the questions/syllabus and is syllabus an id column? — r0ast3d, Oct 24 '11 at 15:44
And I'll give you +1, just for trying `LIMIT 5*(SELECT COUNT(*) ...)` statement. — ypercubeᵀᴹ, Oct 24 '11 at 15:45
Thanks for your help. Currently syllabus is just a text field with the name of the syllabus but I will change that to syllabusID and have a separate table of syllabuses. However I still have the same problem. — TrueWheel, Oct 24 '11 at 19:38

score 1 · Answer 1 · edited May 23 '17 at 11:47

This would work, but would be really slow:

SELECT * FROM questions WHERE difficulty='easy' ORDER BY RAND() LIMIT 5;

A better way is to select 5 ID's first, and then retrieve the rows corresponding to those ID's. Selecting the ID's themselves can be done with a WHERE ID > RAND(0,MAX(ID)), but if there are gaps, then your data will be skewed.

A better alternative is discussed here, but requires more effort: Simple Random Samples from a Sql database

Selecting multiple sets of rows with a single sql query

1 Answers1