sql mysql query for fetching a defined numbers of rows randomly based on condition

Question

I am developing a Online Exam Application where I have a question master table which contains a field ( name : qtype ) containing the data for the type of question. A question type can be either of following three types :

single
multiple
desc

The application is generating a random question paper from this master table by using the following query :

select * from mst_question where test_id = 1 ORDER BY RAND() LIMIT 25

This generates a random questionnaire of 25 questions for my Online Exam Application.

So far Its working good...

Now, I need to implement a feature in my application where the 25 randomly generated questions ( or whatever number - this will depend on the test id ) will always have a FIXED mix of the different types of questions available in the master question table ( mst_question ) for every randomly generated questionnaire set.

Say, if master question table is having 108 questions for a particular test id and all the three types of questions are in the db for this test , it will provide same number of different types questions for each random query !! I have written the following sql query to find out the percentage of each type of question in the master question table.

So far i have tried this and came up with this sql query :

select qtype,count(*) as qtypetotal,(select count(*) from mst_question where test_id = 1) as totalqtype,round((count(*)/(select count(*) from mst_question where test_id = 1)*100),2) as qtypepercentage from mst_question where test_id = 1 group by qtype

The output of above query is :

qtype       qtypetotal  totalqtype  qtypepercentage  
desc        24      108     22.22 %  
multiple    34      108     31.48 %  
single      50      108     46.30 %

I need to form a sql query which will give me 25 randomly generated questions where 22.22% of 25 questions should be desc type , 31.48 % of 25 questions should be multiple type and remaining 46.30 % of 25 questions should be single type.

I am stuck ....... Pls advise...

Thanks in advance :-)

Thanks @MikeBrant.... I have generated the dynamic sql which is definitely the way i wanted... just one issue now.... if i execute this sql query :

select qtype,round(25*(count(*)/(select count(*) from mst_question where test_id = 1))) as numquests from mst_question where test_id = 1 group by qtype

I am getting the following results :

"desc" "6" "multiple" "8" "single" "12"

and based on the above query i generate this dynamic query :

$dynamicsql[] =  "(SELECT * FROM mst_question WHERE test_id = 1 AND qtype = '".trim($rstype->qtype)."' ORDER BY RAND() LIMIT ".$rstype->numquests.")";

$finalsql = implode(" UNION ALL ",$dynamicsql)." ORDER BY RAND()";

I want to generate a total of 25 random questions but the sum of these qtypes is 26 !!! and i am getting a question extra :-(

Try thinking about it this way - you need 24 random questions of type desc for every test... — Clockwork-Muse
– Clockwork-Muse, Commented Jan 30, 2013 at 19:41

ypercubeᵀᴹ · Accepted Answer · 2013-01-30 19:56:57Z

4

I would probably just use a UNION here:

    (SELECT * FROM mst_question
    WHERE test_id = 1 AND qtype = 'desc'
    ORDER BY RAND() LIMIT X)
UNION ALL
    (SELECT * FROM mst_question
    WHERE test_id = 1 AND qtype = 'multiple'
    ORDER BY RAND() LIMIT Y)
UNION ALL
    (SELECT * FROM mst_question
    WHERE test_id = 1 AND qtype = 'single'
    ORDER BY RAND() LIMIT Z)
ORDER BY RAND()

You can use a query like you posed on your original question to get the values for X, Y, and Z to be used.

edited Jan 30, 2013 at 19:56

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Jan 30, 2013 at 19:42

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sandy505 Over a year ago

Error Code : 1221 Incorrect usage of UNION and ORDER BY (0 ms taken)

Mike Brant Over a year ago

@Sandy505 Ah yes. how silly of me. Would actually need to use subselects to make this happen. See updated answer.

Mike Brant Over a year ago

@sgeddes I wasn't intending for those to be actual variables but rather placeholders for values that would be determined by a prior query which would determine the proper mix of questions.

ypercubeᵀᴹ Over a year ago

@MikeBrant: You don't need all the nested tables. It can be simplified.

Mike Brant Over a year ago

@ypercube Would be interested in how you would propose to do this. The intent here was to grab X-Y-Z number of random questions and then randomize the overall order of the questions.

|

Collectives™ on Stack Overflow

sql mysql query for fetching a defined numbers of rows randomly based on condition

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related