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

Question

I know that I can select multiple lines, for example:

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 return the results 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 order I require?

(There are limitations related to how the site is configured, which will not allow me to ORDER BY using a different field value)

Answer 1

, , , ORDER BY - ... , , .

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

Answer 2

ORDER BY, .

, , , MySQL : range , IN, range.

, :

• , 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
  

• :

  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
  

• CASE:

  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 3

You can impose order, but only based on the values ​​(values) of one or more columns.

To return the rows in the order shown in the example, you will need to add a second column called "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;