fetch record in order with IN clause - having duplicate values in IN clause

Question

I have table questions as

id   | question    | question_level
______________________________________
1    | abc         | 1
______________________________________
2    | prs         | 3
______________________________________
3    | oesl        | 2
______________________________________
4    | ocsl        | 3
______________________________________ 
5    | qoindos     | 1
______________________________________
6    | xyz         | 3
______________________________________
7    | mnlop       | 2
______________________________________
8    | cllse       | 2
______________________________________ 
9    | teuosn      | 4
______________________________________
10   | ulcd        | 2
______________________________________

I want to select 10 records which will match with question level I have order of level in which I want all records as below

1,2,1,2,3,2,4,2,3,3

Output should be

id   | question    | question_level
______________________________________
1    | abc         | 1
______________________________________
3    | oesl        | 2
______________________________________
5    | qoindos     | 1
______________________________________
7    | mnlop       | 2
______________________________________ 
2    | prs         | 3
______________________________________
8    | cllse       | 2
______________________________________
9    | teuosn      | 4
______________________________________
10   | ulcd        | 2
______________________________________ 
4    | ocsl        | 3
______________________________________
6    | xyz         | 3
______________________________________

I tried different solutions but couldn't get correct output tried with field, find_in_set but no success. Refered [Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION? but getting only count and record in asceding order

I tried below solutions

SELECT question_level FROM `tbl_questions` WHERE `question_level` IN (1,2,1,2,3,2,4,2,3,3) ORDER BY FIELD(`question_level`, 1, 2, 1, 2, 3, 2, 4, 2, 3, 3) LIMIT 10

SELECT question_level FROM tbl_questions WHERE FIND_IN_SET(`question_level`,'1,2,1,2,3,2,4,2,3,3');

SELECT question_level,question_object_name,question_object_path,question_answer
        FROM tbl_questions e JOIN (SELECT 1 AS question_level UNION ALL
              SELECT 2 UNION ALL
              SELECT 1 UNION ALL
              SELECT 2 UNION ALL
              SELECT 3 UNION ALL
              SELECT 2 UNION ALL
              SELECT 4 UNION ALL
              SELECT 2 UNION ALL
              SELECT 3 UNION ALL
              SELECT 3
             ) matches
             USING (question_level) LIMIT 10;

I tried with foreach loop also but every time getting same record when question level matches with value

$array = explode(',', '1,2,1,2,3,2,4,2,3,3')
foreach ($array as $value) {
 SELECT question_level FROM tbl_questions 
 WHERE question_level = $value;
}

If it is not possible in mysql then can it be achieve using php.

Thanks in advance

It would be useful if you set the table up in a fiddle, such as this https://www.db-fiddle.com/ It's to much of a "tricky" thing for me to do in my head. You can save it an post the link with you question, it's very helpful for DB questions. In your DB you can run `SHOW CREATE TABLE {tablename}` to output the create table syntax. — ArtisticPhoenix, Nov 09 '17 at 06:09
Uh, this is going to be tough because you need ordering plus ranking. I would probaby go down the php loop route with filtering based on rank added to it. — Shadow, Nov 09 '17 at 06:23
are these fields in a second table `question_object_path,question_answer`? — ArtisticPhoenix, Nov 09 '17 at 06:29
Hi. You are clear. Use enough words & sentences & descritions. If you can't clearly explain what you want in natural language how can you expect to express it as code? Please read & act on [mcve]. PS Tables have no order, result sets [sic] do. Read about SQL window functions. For MySQL you can give an ordering value with each each level value or you can insert in a loop from an array or list. People try to increment a variable in a select but reading & updating a variable in the same statement is undefined behaviour. — philipxy, Nov 09 '17 at 14:16

ArtisticPhoenix · Answer 1 · 2017-11-09T07:36:56.400

Logically it's not possible to do what you want, I'll try to explain using you order choice

 ORDER BY FIELD(`question_level`, 1, 2, 1, 2, 3, 2, 4, 2, 3, 3)

So here we want 1 then 2 then 1 again of the question_level. Now in the table We have these values. I'll concern myself with just question_level #1 which is all I need to show my point.

id   | question    | question_level
______________________________________
1    | abc         | 1
______________________________________
5    | qoindos     | 1
______________________________________

As you can see we have rows for question_level=1 Now how would the system decide which level 1 is first and which is second. There is no way to decide how to do this. So regardless of the Database failing to sort it the way you want. Without more information to make that choice there is no way to construct a loop even to sort that. The best you could do is sort by the primary key, and then the level. Which you would have to do server side most likely.

I think the mistake you made here is, you need to use the questions actual unique ID, if you have limits on how many questions at each level then that needs to be addressed separately.

Hopefully that makes sense.

If you were trying to select x number of random questions at n level that could be worked out fairly easily. For example if you wanted

2x questions at lv 1
4x questions at lv 2
3x questions at lv 3
1x questions at lv 4.

This could be worked out with four simple queries for the level, while sorting randomly on the id field and using an appropriate limit clause for that level. By they way these are the numbers of levels in your question.

IF you do want to select random questions at a given level, you may want to do it with a subquery. RAND() has some performance penalties which you should be able to skirt around by just randomizing the primary key and then joining on the table to pull the rest of the data out once its ordered. But, you should benchmark it.

So an example of that would be this.

SELECT
    q1.*
FROM 
    tbl_questions AS q1
JOIN
(
    SELECT
        id
    FROM
        tbl_questions
    WHERE
        question_level = 1
    ORDER BY RAND() LIMIT 2
) AS q2 USING( id )

Although I have to admit I've never tried this just an Idea I had.

I am trying to sort it out using php foreach loop. Thanks for your help. — Nagesh Katke, Nov 09 '17 at 10:06
Thanks for your help I sorted this out using php and foreach. — Nagesh Katke, Nov 10 '17 at 05:00

score 0 · Accepted Answer · answered Nov 13 '17 at 07:36

I sorted this out using php

$sequence = "1,2,1,2,3,2,4,2,3,3";
$seq_arr = explode(',', $sequence);

Step 1:

Created array to store all details

$questions = array(); // to store all questions 
$final_questions = array(); // tos store final questions in the sequence needed
$level_1 = array(); // level 1 questions
$level_2 = array(); // level 2 questions
$level_3 = array(); // level 3 questions
$level_4 = array(); // level 4 questions
$level_5 = array(); // level 5 questions

Step 2 :

Select all array from Db and store in each array according to question level

$this->db->select(*);
$this->db->from(questions);
$query = $this->db->get();
$result = $query->result_array();
for ($i=0; $i<count($result) ; $i++) {
 if(isset($result[$i]) && !empty($result[$i])){
  if($result[$i]['question_level'] == 1)
  {
    $level_1[] = $result[$i];
  }
  if($result[$i]['question_level'] == 2)
  {
    $level_2[] = $result[$i];
  }
  if($result[$i]['question_level'] == 3)
  {
    $level_3[] = $result[$i];
  }
  if($result[$i]['question_level'] == 4)
  {
    $level_4[] = $result[$i];
  }
  if($result[$i]['question_level'] == 5)
  {
    $level_5[] = $result[$i];
  }
 }
}

Step 3 :

Match sequence with all array and store into final array.

foreach ($seq_arr as $key => $value) {
  if($value == 1)
    {
         for ($i=0; $i < count($difficulty_array) ; $i++) 
          { 
             if(isset($level_1[$i]) && !empty($level_1[$i])){
               if(!in_array($level_1[$i], $final_questions))
                 {
                    $final_questions[] = $level_1[$i];
                    break;
                 } 
             }
          }
     }
   if($value == 2)
     {
         for ($i=0; $i < count($difficulty_array) ; $i++) 
          { 
             if(isset($level_2[$i]) && !empty($level_2[$i])){
               if(!in_array($level_2[$i], $final_questions))
                 {
                    $final_questions[] = $level_2[$i];
                    break;
                 } 
              }
           }
      }
    if($value == 3)
      {
        for ($i=0; $i < count($difficulty_array) ; $i++) 
         { 
            if(isset($level_3[$i]) && !empty($level_3[$i])){
               if(!in_array($level_3[$i], $final_questions))
                 {
                    $final_questions[] = $level_3[$i];
                    break;
                 } 
             }
          }
     }
     if($value == 4)
       {
          for ($i=0; $i < count($difficulty_array) ; $i++) 
            { 
              if(isset($level_4[$i]) && !empty($level_4[$i])){
                if(!in_array($level_4[$i], $final_questions))
                   {
                     $final_questions[] = $level_4[$i];
                     break;
                   } 
              }
            }
       }
     if($value == 5)
       {
          for ($i=0; $i < count($difficulty_array) ; $i++) 
             { 
                if(isset($level_5[$i]) && !empty($level_5[$i])){
                   if(!in_array($level_5[$i], $final_questions))
                      {
                         $final_questions[] = $level_5[$i];
                         break;
                       } 
                 }
             }
          }
      }
print_r($final_questions);

fetch record in order with IN clause - having duplicate values in IN clause

2 Answers2

Linked