How to order alphabetically EXCEPT for one item?

Databases
1

Greetings,

I know how to order a list of items alphabetically:

$sql = "SELECT name FROM categories ORDER BY name";

It produces a list like this:
A…
B…
C…
Food
Other
Tools
Water
X…
Y…
Z…

I would like the category name “Other” to appear at the very bottom of the list as it should be chosen if the other items of the list do not apply. How can this be done?

Thanks

2 
SELECT name
FROM categories
ORDER BY name='Other' ASC, name ASC
3

aamonkey, although your code works, it relies on a quirk of mysql whereby an equality comparison, which evaluates true or false, is interpreted as 1 or 0

i think for the benefit of simplicity and clarity, an explicit CASE expression, which is standard SQL and will work in any database system, is better –

ORDER
    BY CASE WHEN name = 'Other'
            THEN 1 -- last
            ELSE 0 -- first
        END ASC
     , name ASC
4

Thanks a lot for the help r937. I haven’t come across CASE before so this is some neat stuff I can study about as well!

Kind regards

5

That’s nice r937 - didnt know about that syntax

6

There is nothing unstandard in sorting by a boolean value. FALSE is defined as being less than TRUE. It is not as portable between different DBMS as the case expression though.