SELECT 'random' results with set parameters

Question

I am developing a system which selects questions from a database to generate a test/exam.

Each question has a set 'question type' ('q_type'). I have a database of questions and need to select 4 questions, each with a different 'q_type'.

The basic query to select 4 random questions at the moment is:

SELECT * FROM questions ORDER BY RAND() LIMIT 0,4

This obviously does not take into account the fact that each question should have a different 'q_type'.

I would like to be able to do something that follows this logic (i need something to fill in the square brackets):

SELECT * FROM questions WHERE ['q_type' is DISTINCT] ORDER BY RAND() LIMIT 0,4

I have tried using GROUP BY 'q_type', but that simply gives the first question for each 'q_type', not a different question of that type each time.

Any help would be great, as I am completely stumped at the moment (I am working with a overblown PHP loop which simply queries the DB 4 times, with an updated WHERE 'q_type'!=XX each time).

Thanks for any help!

Answer 1

i'm by no means an SQL expert but does this work?

SELECT DISTINCT `q_type`, * FROM questions ORDER BY RAND() LIMIT 0, 4

Answer 2

I don't think there's an easy way to do this with simple queries. You could probably do it with a mysql stored procedure, or to save some development time do it in php.

If the pool of questions isn't something extremely large, and this isn't something that happens too frequently, you should be ok with the 4 separate queries (one for each q_type), or even with getting the entire question set into php and then playing around with it to get 4 random questions, one in each q_type.

With the following (untested) code you'll be getting all questions ordered randomly and loop through them to get the first question for each q_type, then stop. It's not the most elegant solution but, again, for small enough data sets and frequency it should be ok.

$questions = array() ;
$res = mysql_query('SELECT * FROM questions ORDER BY RAND()') ;
while ($row = mysql_fetch_assoc($res)) {

    //if we have all 4, stop
    if (count($questions) == 4) {
        break ;
    }

    $currType = $row['q_type'] ;
    $currQuestion = $row['questionTitle'] ;

    if (isset($questions[$currType])) {
        //already have it, continue to the next
        continue ;
    }

    $questions[$currType] = $currQuestion ;

}