MySQL Cannot retrieve repeated rows using SELECT IN

Question

I am unable to retrieve rows with repeated IDs using SELECT IN

ID column is Primary key with auto increment.

SELECT ID FROM dbproduct WHERE ID IN (3,2,1,4,3,2,1,4);

I get the following result back

 ID
-----
  1
  2
  3
  4
------

Other than that it doesnt seem to be displaying result in the order it was sent.

Im looking for a query to get the desired result is this

 ID
-----
  3
  2
  1
  4
  3
  2
  1
  4
------

cpsrvd 11.86.0.27

Database client version: libmysql - 5.6.43

PHP version: 7.3.6

score 0 · Accepted Answer · answered Sep 26 '20 at 13:19

You can put these numbers in a derived table first (along with their sequence in the list), then join:

select p.*
from (
    select 3 id, 1 seq
    union all select 1, 2
    union all select 1, 3
    union all select 4, 4
    union all select 3, 5
    union all select 2, 6
    union all select 1, 7
    union all select 4, 8
) n
inner join dbproduct p on p.id = n.id
order by n.seq

score 0 · Answer 2 · answered Sep 26 '20 at 13:26

0

You can update mysql to Ver 8.0.21. It's work.

answered Sep 26 '20 at 13:26

KeyGenQt

129
1
5

unfortunately its a shared hosting and I cannot change mysql version. – Taha Khan Sep 26 '20 at 13:32
https://i.imgur.com/j5TRxua.png mysql Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu)) – KeyGenQt Sep 26 '20 at 14:03

MySQL Cannot retrieve repeated rows using SELECT IN

2 Answers2