IN() order in mysql

Asked

Viewed 42 times

2

Well I’m making the following select:

select * from pedidos where id IN (1,3,4,2)

Have any way mysql return me the result in the same order it is in IN?

With the select I’m making mysql is putting in ascending order and I don’t want it to happen.

mysql

2 answers

3


You can use the operator FIELD

SELECT * 
  FROM pedidos 
 WHERE id IN (1,3,4,2)
ORDER BY FIELD(id, 1, 3, 4, 2)

The operator operates 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 id records 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);
  • 1

    that’s right, thank you.

3

Just as a curiosity, an alternative to FIELD():

SELECT * 
FROM pedidos 
WHERE id IN (1,3,4,2)
ORDER BY id = 2, id = 4, id = 3, id = 1;

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.