avoiding mysql default order

[guhels]

Hello there,

Is there a way to avoid mysql default ORDER BY indexname ASC behavior? I have tried ORDER BY NULL with no results...

Thanks in advance.

[dagon]

ALTER TABLE foo ORDER BY bar

at least i think that's what your asking.

[Bjom]

What do you want to achieve?

[guhels]

Im using a SELECT with a WHERE IN statement, in my WHERE IN im using a array of ids thats in a particular order, lets say 8, 1, 2. The problem is that MySQL reorder it based on the index wich is the id column itself. It will give my results back, but in a ASC order: 1, 2, 8.

I want to be able to keep the array order, is that possible?

[Bjom]

What's the criterion for you to put 8 before 1 before 2 in this example? Can you order by that?

[guhels]

Im using Sphinx (http://www.sphinxsearch.com) and thats the order that he gives me after a search, the criterion is what sphinx judges more relevant, the content in id 8 is more relevant then the one in id 1 wich is more relevant the the one in id 2.
Thats the reason i really need to keep that order.

[Bjom]

In that case you can either retrieve the records one by one and put them in an array (only viable for few records), or you create a temporary table with two fields, one containing the ID, the other the rank, then join that on the data-table (a bit of an overhead here so only good for many records)

That are the two alternatives, that I can come up with - they both work, but none of them is really elegant.

[Lars_Berg]

This will work if you have few values to order by

order by 
    case columnName
    when 8 then 1
    when 1 then 2
    when 2 then 3 end