Selecting multiple sets of rows with a single sql query

manpreet Best Answer 3 years ago

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.