MySQL ORDER problem

Question

I have a pretty simple SQL query but something is missing and I didn't find an answer for this problem yet. The thing is that I select some fields with several ids and I want the result to be ordered in this particular order.

The query is the following

SELECT `content`.* 
FROM   `content` 
WHERE  (user_id = "1" AND ( id = "4" OR id = "7" OR id = "5" OR id = "8" ))

The default order is "id ASC" (id is my primary key), but I want the order to be 4,7,5,8 in this particular case.

Any ideas?

Answer 1

ORDER BY CASE user_id  
    WHEN "4" THEN 1  
    WHEN "7" THEN 2  
    WHEN "5" THEN 3  
    WHEN "8" THEN 4  
    ELSE 5  
END

If you want to generalize it, you can create a new table with two columns - "user_id" and "order_by_val", join to it, and "ORDER BY order_by_val".

---

EDIT: Per @Tim, MySQL has a proprietary function - FIELD() - as noted in his post, if you aren't concerned about portability, or the issue that function return values are unoptimizable.

Answer 2

The way I'd normally do this sort of odd ordering is use a case statement:

order by case id
             when "4" then 1
             when "7" then 2
             when "5" then 3
             when "8" then 4
             else 99
         end

Not sure if this translates directly to mysql but the idea should.

Answer 3

ORDER BY CASE id WHEN 4 THEN 1 
                 WHEN 7 THEN 2 
                 WHEN 5 THEN 3 
                 WHEN 8 THEN 4 
                 ELSE 99
         END;

Answer 4

You can try adding the following to the end of your query.

ORDER BY CASE ID WHEN 4 THEN 1 WHEN 7 THEN 2 WHEN 5 THEN 3 WHEN 8 THEN 4 ELSE 5 END

Answer 5

This will do what you want:

select * 
from myTable
order by field(myID, 8, 7, 6) desc;

You can set the order of whatever ID's (or whatever) youwant to appear, and any others will follow after that. Hope that helps.

More on this @ http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field