Order MySQL in Select .. In a statement

Question

In MySQL, I have SQL like

select id from user where id IN (2, 3, 1);

But MySQL returns the string as 1, 2, 3, is it possible to get MySQL to return the order, the same as my original order 2, 3, 1?

Thank.

+3

Howard Mar 18 '11 at 15:38

3 answers

For portable code use CASE/WHEN. It is supported by all (basic) dbms.

order 
   by case when id = 2 then 1
           when id = 3 then 2
           when id = 1 then 3
       end

+1

Ronnis Mar 18 '11 at 22:28

You can also order LOCATE as follows:

SELECT
    `id`
FROM
    `user`
WHERE
    `id` IN(2, 3, 1)
ORDER BY
    LOCATE(`id`, "2 3 1")

Felix

0

Felix geenen Mar 18 '11 at 15:45

Eugene yarmash · Accepted Answer · 2011-03-18T15:40:06+0000

You can use the function FIELD:

select id from user where id IN (2, 3, 1) ORDER BY FIELD(id, 2, 3, 1);