Getting rows maintaining the order of the IDs passed

I have this clause:

SELECT *
  FROM picture p
 WHERE p.id IN (2, 1, 3)

When I execute it I get the 3 elements, but ordered by its 'id' (1,2,3).

What I want is to get the elements in base of the order I'm givin开发者_Python百科g: 2,1,3.

When I execute it I get the 3 elements, but ordered by its 'id' (1,2,3).

Not really. You basically get an arbitrary order. No ORDER BY clause means no reliable order.

If you are building your SQL dynamically, you'll have to do something like this:

SELECT *
FROM picture p
WHERE p.id IN (2, 1, 3)
ORDER BY CASE p.id
    WHEN 2 THEN 1
    WHEN 1 THEN 2
    WHEN 3 THEN 3
END

Besides the CASE you can also use FIELD() function:

SELECT   *
  FROM   picture p
 WHERE   p.id IN (2, 1, 3)
ORDER BY FIELD(p.id, 2, 1, 3)

SELECT     *
FROM         picture p
WHERE
p.id = 2

UNION
SELECT     *
FROM         picture p
WHERE
p.id = 1

UNION
SELECT     *
FROM         picture p
WHERE
p.id = 3

source : Click here

And kindly don't use "*" in SELECT statement it's bad practice; try to read about rule of thumb for database optimization.