How to SELECT in MySQL data based on percentage given?

Question

I have the following query to select question ids in table_question:

$sql = "SELECT s.question_id
        FROM table_question s
        WHERE s.chapter_id = $chapter_id
        AND s.publish_status = ".PUBLISHED_ACTIVE." AND (
        s.format_type_id = ".QST_TYPE_OBJECTIVE." OR (s.format_type_id = ".QST_TYPE_SHORT_ANSWER." AND s.question_id < 200000 AND s.publish_status = ".PUBLISHED_ACTIVE."))
        ORDER BY RAND() LIMIT 5";

$obj_question_ids = DB::connection('mysql_question')->select(DB::raw($sql));

In my database, I got 133010 of objective questions and 15031 subjective questions. Now, how I can make sure the ratio of my question selection will be

70% Objectives questions and 30% questions will be structure questions?

As @GordonLinoff says: 70% of 5 answers is 3.5, whcih is impossible. AT BEST you can get 60% - 40 % on 5 answers. And that's the solution, random 3 questions from objective and 2 from subjective. — Jacques Amar, Oct 17 '17 at 01:23
@JacquesAmar it seems your answer quite exact what I meant. Now, how to random for 3 question objective and 2 subjective on the same query? — Nere, Oct 17 '17 at 01:26

Jacques Amar · Accepted Answer · 2017-10-17T02:18:05.677

This might work

Let's try with parenthesis

usage of union

   (SELECT s.question_id
            FROM table_question s
            WHERE s.chapter_id = $chapter_id
            AND s.publish_status = ".PUBLISHED_ACTIVE." AND (
            s.format_type_id = ".QST_TYPE_OBJECTIVE." OR (s.format_type_id = ".QST_TYPE_SHORT_ANSWER." AND s.question_id < 200000 AND s.publish_status = ".PUBLISHED_ACTIVE."))
            ORDER BY RAND() LIMIT 3)
    UNION
    (SELECT s.question_id
            FROM table_question s
            WHERE s.chapter_id = $chapter_id
            AND s.publish_status = ".PUBLISHED_ACTIVE." AND (
            s.format_type_id = ".QST_TYPE_SUBJECTIVE." OR (s.format_type_id = ".QST_TYPE_SHORT_ANSWER." AND s.question_id < 200000 AND s.publish_status = ".PUBLISHED_ACTIVE."))
            ORDER BY RAND() LIMIT 2)

How to SELECT in MySQL data based on percentage given?

1 Answers1