Select random 10 unique rows from MySQL database?

Question

I have one table (questions) with 54 questions.

The students have to answer all 54 questions.

I have to show 10 questions at once, then next 10 and so on....

I have to select 10 questions randomly and next 10 random questions except the previous 10 questions that already answered, and so on....

I store all the answers in another table named user_answer.

what will be the MySQL Query?

Answer 1

You can do this by using following query.

SELECT * FROM questions WHERE question_id NOT IN (SELECT question_id FROM student_answers WHERE student_id = '5') ORDER BY RAND() LIMIT 10;

I have assumed that there is 3 tables.

1. question - Hold all questions
2. student_answers - hold student specific answers of each question.
3. student - hold student informations.

I expect that student_id is the primary key of student table and foreign key of student_answer table.

The student_is is brought from session or somewhere.

Answer 2

In pure SQL I'd probably do something like this, assuming you save the answers for each 10 answers. Wouldn't reccomend it for huge tables though, NOT IN is kind of slow.

SELECT ... FROM questions 
WHERE question_id NOT IN (SELECT question_id FROM user_answer 
    WHERE user_id = <user_id>) 
ORDER BY RAND() LIMIT 10;

Another way would be getting all the question ID's and keeping track of them in the application.

Answer 3

SELECT ... FROM tbl ... ORDER BY RAND() LIMIT 10;

gets you a random 10. But this is not sufficient to avoid repeating some of them for the next 10. So let's get a bit more sophisticated. Let's prime the random number generator (with a "seed") so that the sequence is repeatable, then use OFFSET to pick which 10.

If this is being handled by web pages, then the seed must be passed from one page to the next, similar to how you use to decide that it is time for the "next 10" questions.

Assuming you don't care if the sequence repeats itself for a second...

$seed = SELECT UNIX_TIMESTAMP();
SELECT ... FROM tbl ORDER BY RAND($seed) LIMIT 10 OFFSET $offset;

Then remember $seed, but increment $offset by 10 for the next 'page'.

You, of course, need some way to stop after you run out of questions.