Mysql – Select random 10 unique rows from MySQL database

MySQLrandom

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?

Best Answer

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.

question - Hold all questions
student_answers - hold student specific answers of each question.
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.

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Mysql – Database design – many to many

Considering that the question is labeled database-design I will offer a solution without that CSV field. Even if you decide to keep it, this may offer some ideas.

Question contains all possible questions.
Answer has all possible answers (to any question)
QuestionAnswer contains all QA combinations offered, note that there must exist a default answer for "I do not know".
UserQA contains questions user answered.
Note that rule "There can be one and only one correct answer for each question" (IsCorrect = 'Y'), has to be implemented on the application level or as a stored procedure/trigger.

enter image description here

To get questions that a user with specific_UserId has not yet answered

select  qq.QuestionID
from Question as qq
where not exists (select 1 
                    from UserQA as xx 
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );

Or, better if there may be some new questions which do not have defined answers yet

select distinct qq.QuestionID
from QuestionAnswer as qq
where not exists (select 1 
                    from UserQA as xx 
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

Mysql – Database design – many to many

Related Question