How to not automatically sort IN(Mysql)

Asked

Viewed 106 times

4

Run this SQL command:

SELECT * FROM produtos WHERE id
IN (144,140,134,133,128,129,141,143,149,150,147,146,126,142,125,99,100,92,91,90,108,109,123,124,122,121,110,89) 
ORDER BY none

But the consultation comes with Ids ordered, how to prevent this?

mysql

  • And what an order you’d like to apply?

  • the order I specified in..

  • Try so using the Field: SELECT * FROM produtos WHERE id in(144,140,134,133,128,129,141,143,149,150,147,146,126,142,125,99,100,92,91,90,108,109,123,124,122,121,110,89) ORDER BY FIELD(id, 144,140,134,133,128,129,141,143,149,150,147,146,126,142,125,99,100,92,91,90,108,109,123,124,122,121,110,89);

  • I believe the answer here can help you: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause

2 answers

4


The result of a SELECT is sorted through the INDEX definitions in your table. For example, if your table has the column id as primary KEY, it will be an INDEX of your table automatically, although it is possible to define other.

Instead of not ordering, what you want is a custom ordering. If I’m right, you get this effect by using the function FIELD:

SELECT * FROM produtos WHERE id IN (144, 140, 134, ...)
ORDER BY FIELD(id, 144, 140, 134, ...)

2

You can use the function FIELD:

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in str1, str2, str3,... list. Returns 0 if str is not found.

Example:

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

See demonstração

Your SQL command looks like this:

SELECT * FROM produtos 
WHERE id IN 
(144,140,134,133,128,129,141,143,149,150,147,146,126,142,125,99,100,92,91,90,108,109,123,124,122,121,110,89) 
ORDER BY FIELD 
(id, 144,140,134,133,128,129,141,143,149,150,147,146,126,142,125,99,100,92,91,90,108,109,123,124,122,121,110,89);

Browser other questions tagged mysql

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