mysql ordering exception

I have a MySQL database and a have a funny question.

I need to order the results of a query by a field which has entries of 1,2,3 or 4, ordered descending but with 4 at the end.

So I wish t开发者_StackOverflowo have the results in the following order

3's 2's 1's 4's

Is this possible at all?

I know I can do order the result array in php but unfortunately I need to do this in an sql statement.

If the field is an int,

ORDER BY (fieldname = 4) DESC, fieldname DESC

should do the trick.

Here is another cool way

ORDER BY MOD(fieldname,4) DESC,fieldname

If the result is a CHAR(1) then

ORDER BY LOCATE(fieldname,'3214'),fieldname

add this to the order

ORDER BY CASE field_name WHEN 4 THEN 1 ELSE 2 END

this will return the result of the query order using the value of the field