Order by desc except

Question

Here is the query with result:

SELECT id, name FROM medic WHERE x=z ORDER BY name ASC

1. Aric
2. Bodi
3. Copi
4. Dori
5. Edo
6. Other
7. Poyo
8. Quex
9. Razix
10. Zika

What best if I need to display "Other" at the last as result below:

1. Aric
2. Bodi
3. Copi
4. Dori
5. Edo
7. Poyo
8. Quex
9. Razix
10. Zika
6. Other

Answer 1

In MySQL, you can just use the boolean expression:

ORDER BY (name <> 'Other') DESC,
         name

MySQL treats boolean expressions as numbers in a numeric context, with "1" for true and "0" for false. (I happen to like this convention and wish other databases had similar support.) This can be convenient in these cases.

Answer 2

One option is to order using a CASE expression:

SELECT
    id,
    name
FROM medic
WHERE x=z
ORDER BY
    CASE WHEN name <> 'Other' THEN 0 ELSE 1 END,
    name