I 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 query comes with the IDs sorted, how to prevent this?
How not to automatically sort IN (MySQL)
4
asked by anonymous 01.06.2015 / 22:33
2 answers
4
The result of a SELECT is sorted through the INDEX definitions in your table. For example, if your table has the id column as PRIMARY KEY, it will be an INDEX from your table automatically, although you can define others.
Instead of non-ordering, what you want is custom ordering. If I'm correct, you can do this using the FIELD function:
SELECT * FROM produtos WHERE id IN (144, 140, 134, ...)
ORDER BY FIELD(id, 144, 140, 134, ...)
01.06.2015 / 22:59
2
You can use FIELD function :
FIELD ( str, str1, str2, str3, ... )
Returns the index (position) of
strin str1 , str2 , str3 , ... < in>. Returns 0 ifstris not found.
Example:
SELECT * FROM produtos WHERE id IN (1, 2, 3, 4) ORDER BY FIELD(id, 4, 2, 1, 3);
Your SQL statement 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);
01.06.2015 / 23:08