How to order rows in MySQL when using in

Question

I am using this query to select rows in MySQL

SELECT * FROM `table` WHERE `id` IN (5,2,3,1,4);

Now by default the rows will be ordered by their id: 1, 2, 3, 4, 5

Is there any way that I can order rows as they are in the query, so the order should be: 5, 2, 3, 1, 4 ?

score 2 · Accepted Answer · answered Mar 13 '15 at 22:37

2

Use MySQL's FIELD() function

SELECT * 
  FROM `table` 
 WHERE `id` IN (5,2,3,1,4)
 ORDER BY FIELD(`id`, 5,2,3,1,4)
;

answered Mar 13 '15 at 22:37

Mark Baker

209,507
32
346
385

score 2 · Answer 2 · answered Mar 13 '15 at 22:38

You can use case statement to customize the sort

SELECT *
FROM   table
WHERE  id IN ( 5, 2, 3, 1, 4 )
ORDER  BY CASE id
            WHEN 5 THEN 1
            WHEN 2 THEN 2
            WHEN 3 THEN 3
            WHEN 1 THEN 4
            WHEN 4 THEN 5
            ELSE 6
          END

How to order rows in MySQL when using in

2 Answers2