CODEWITHSUNDEEP
mysqlsql-order-by
Keith
Keith

Reputation: 1185

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 to 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.

Upvotes: 2

Views: 537

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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

Upvotes: 2

Pekka
Pekka

Reputation: 449435

If the field is an int,

ORDER BY (fieldname = 4) DESC, fieldname DESC

should do the trick.

Upvotes: 8

hish
hish

Reputation: 145

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

Upvotes: 1

Related Questions