MySQL ORDER BY [custom SET field value]

Question

Hi there!

I hop 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

Nevermind.

The trick is saving SET values at the right order.

Answer 2

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