MySQL ORDER BY [custom SET field value]

Question

I hope there's a simple solution for this:

I have a table where each row has it's own status (SET type field). Statuses can be:

• offline
• available
• busy
• distance

I'd like to order as follows

• available
• busy
• distance
• offline

I thought a simple

ORDER BY `status` ASC

will do the trick (alphabetical order) but it gives me the following:

• offline
• available
• busy
• distance

How can is sort out this in the most simple way?

Thanks in advance,

fabrik

Answer 1

SELECT * FROM table ORDER BY FIELD(status,'offline','available','busy','distance')

see Mysql order by specific ID values

Answer 2

Thought I'd add another way to order by custom field values,

ORDER BY FIND_IN_SET(status, 'available,busy,distance,offline')

(If the given strings contain a quote simply escape it)

Answer 3

You could also do something like this, if reordering the SET values in impractical:

... ORDER BY CASE `status` 
                WHEN 'available' THEN 1
                WHEN 'busy' THEN 2
                WHEN 'distance' THEN 3
                WHEN 'offline' THEN 4
             END