Is there a way to include duplicates from MySQL query: select..from..where..id in (list)

Question

I am trying to get the results for the query type

SELECT * FROM table WHERE id IN(2,4,6,1,1,2) ORDER BY field (id,2,4,6,1,1,2)

and I want to get results in the same order as the list including : the duplicates. The above query retains the order but cuts out duplicates. I know I can post-process the results but just wondering if there is an easier way.

Thanks

Answer 1

This will actually achieve what you want:

SELECT * FROM table
inner join (
   select 1 as sort, 2 as value union all
   select 2, 4 union all
   select 3, 6 union all
   select 4, 1 union all
   select 5, 1 union all
   select 6, 2) X on X.value=table.id
ORDER BY X.sort