I'm doing the following select:
select * from pedidos where id IN (1,3,4,2)
Is there any way for mysql to return the result in the same order that it is in IN?
With the select that I'm doing mysql is putting in ascending order and I do not want that to happen.
You can use the FIELD operator.
SELECT *
FROM pedidos
WHERE id IN (1,3,4,2)
ORDER BY FIELD(id, 1, 3, 4, 2)
The operator works as follows:
FIELD() devolve a posição de um determinado valor (caso este exista) na lista delimitada por virgula.
Se id = 1, FIELD(id,3,2,1,4) devolve 3 (posição do 1 na lista)
Se id = 2, FIELD(id,3,2,1,4) devolve 2 (posição do 2 na lista)
Se id = 4, FIELD(id,3,2,1,4) devolve 4 (posição do 4 na lista)
Se id = 5 ou outro valor que não existe na lista, FIELD(id,3,2,1,4) devolve 0
Knowing this, you can use FIELD to control sorting. If you want the records with id in the list (1, 3, 4, 2) to be listed at the top, just do:
SELECT *
FROM pedidos
WHERE id IN (1,3,4,2)
ORDER BY IF(FIELD(id, 1, 3, 4, 2) = 0, 1, 0), FIELD(id, 1, 3, 4, 2);
Just curious, an alternative to FIELD () :
SELECT *
FROM pedidos
WHERE id IN (1,3,4,2)
ORDER BY id = 2, id = 4, id = 3, id = 1;