In MYSQL, how can I select multiple rows and have them returned in the order I specified?

Question

Hi,

I know I can select multiple rows like this:

select * FROM table WHERE id in (1, 2, 3, 10, 100);

And I get the results returned in order: 1, 2, 3, 10, 100

But, what if I need to have the results returned in a specific order? When I try this:

select * FROM table WHERE id in (2, 100, 3, 1, 10);

I still get the results returned in the same order: 1, 2, 3, 10, 100

Is there a way to get the results returned in the exact order that I ask for?

(There are limitations due to the way the site is set up that won't allow me to ORDER BY using another field value)

Answer 1

Unless you are able to do ORDER BY, these is no guaranteed way.

The sort you are getting is due to the way MySQL executes the query: it combines all range scans over the ranges defined by the IN list into a single range scan.

Usually, you force the order using one of these ways:

1. Create a temporary table with the value and the sorter, fill it with your values and order by the sorter:

   CREATE TABLE t_values (value INT NOT NULL PRIMARY KEY, sorter INT NOT NULL)
   
   
   INSERT
   INTO    t_values
   VALUES
   (2, 1),
   (100, 1),
   (3, 1),
   (1, 1),
   (10, 1);
   
   
   SELECT  m.*
   FROM    t_values v
   JOIN    mytable m
   ON      m.id = v.value
   ORDER BY
           sorter
   

2. Do the same with an in-place rowset:

   SELECT  m.*
   FROM    (
           SELECT  2 AS value, 1 AS sorter
           UNION ALL
           SELECT  100 AS value, 2 AS sorter
           UNION ALL
           SELECT  3 AS value, 3 AS sorter
           UNION ALL
           SELECT  1 AS value, 4 AS sorter
           UNION ALL
           SELECT  10 AS value, 5 AS sorter
           )
   JOIN    mytable m
   ON      m.id = v.value
   ORDER BY
           sorter
   

3. Use CASE clause:

   SELECT  *
   FROM    mytable m
   WHERE   id IN (1, 2, 3, 10, 100)
   ORDER BY
           CASE id
           WHEN 2 THEN 1
           WHEN 100 THEN 2
           WHEN 3 THEN 3
           WHEN 1 THEN 4
           WHEN 10 THEN 5
           END
   

Answer 2

You can impose an order, but only based on the value(s) of one or more columns.

To get the rows back in the order you specify in the example you would need to add a second column, called a "sortkey" whose values can be used to sort the rows in the desired sequence, using the ORDER BY clause. In your example:

Value    Sortkey
-----    -------
  1         4
  2         1
  3         3
 10         5
100         2

select value FROM table where ... order by sortkey;

Answer 3

the way you worded that I'm not sure if using ORDER BY is completely impossible or just ordering by some other field... so at the risk of submitting a useless answer, this is how you'd typically order your results in such a situation.

SELECT *  
FROM table  
WHERE id in (2, 100, 3, 1, 10) 
ORDER BY FIELD (id, 2, 100, 3, 1, 10)