Random

[NaniG]

Hi to All,
 
     I have a table with fields TOPIC_ID and QID, multiple QID's for TOPIC_ID
 
Table A :
 
TOPIC_ID     QID

    1                 1

    1                 2

    1                 3

    2                 4

    2                 5

    2                 6

    3                 7

    3                 8

    3                 9

 

Now my question is : how can i get the random QIDs of each TOPIC_ID and QID LIMIT should be 2 for each TOPIC_ID.

 

My Query is

 

"select * from questions where TOPIC_ID in (1,2,3) order by rand() limit 0,6";

 

When i tried with above mentioned query, am getting the randomly generated QIDs. i am getting the correct output.

 

But my intension is to get only 2 QIDs from each TOPIC_ID and it should be randomly generated QID. How can i.

 

Please help me out.

 

 

 

[Barand]

Selecting two random questions from three is the same as omitting a random question. Use a subquery to select a randon question from each group the use a left join to omit the matching question.

SELECT q.topic_id, q.qid
FROM questions q
LEFT JOIN
    (
    SELECT topic_id, FLOOR(MIN(qid) + RAND()*(MAX(qid)-MIN(qid))) as qid 
    FROM questions
    GROUP BY topic_id
    ) as random
    USING (topic_id, qid)
WHERE random.qid IS NULL

[NaniG]

Thanks for your quick reply Barand,

 

        IT seems to be fine for all the Topic Ids.

 

if the Topic Id 1 is realted to Topic id 3 and Topic Id 2 is also related to Topic Id 3 and there is no relationship in between 1 and 2. Then how can get the random QIDs.

 

Let us suppose

 

Topic Id 1 -> Java

Topic Id 2 -> .Net

Topic Id 3 -> Javascript

 

There is relation inbetween 1 and 3 , 2 and 3 but not with 1 and 2.

 

So my question is how can i get the Questions of 1 and 3 or 2 and 3 Topic IDs which is random and each topic must contain atleast 2 Questions (for example).

 

Hope i am clear.

[Barand]

Not sure how they are related or what you mean at all but this would be a more general solution using MySql and PHP

$chosen_topics = '1,3';
$q_per_topic = 2;

$sql="SELECT topic_id, qid
    FROM questions
    WHERE topic_id IN ($chosen_topics)";
$qs = $chosen = array();
$res = $db->query($sql);
while (list($tid,$qid) = $res->fetch_row()) {
    $qs[$tid][] = $qid;
}
foreach ($qs as $tid=>$qarray) {
    $selects = array_rand($qarray, $q_per_topic);
    foreach ($selects as $s) {
        $chosen[$tid][] = $qarray[$s];
    }
}

foreach ($chosen as $tid => $qselected) {
    printf ("Topic %s : Questions %s<br>", $tid, join(', ', $qselected));
}

/*** OUTPUT ********************
Topic 1 : Questions 2, 3
Topic 3 : Questions 7, 8
*/