Mysql complex order by

I have 3 columns in my table

1. status enum('Completed','Incomplete','In Progress')
2. severity enum('High','Low','Moderate')
3. created datetime

Now currently i am ordering by severity ASC created DESC status DESC. which is not working right. This is how i want it to be listed.

1. Incomplete - High
2. Incomplete - moderate
3. Incomplete - low
4. In Progress -开发者_JAVA技巧 High
5. In Progress - moderate
6. In Progress - low
7. Completed - High
8. Completed - moderate
9. Completed - low

You can use a case to order rows like that:

select  *
from    YourTable
order by
        case 
        when status = 'Incomplete' and severity = 'High' then 1
        when status = 'Incomplete' and severity = 'Moderate' then 2
        ...
        end

As well as Andomar's solution, I'd consider having proper tables for status and severity. Not enums.

You can imply sort order from the key of these tables, but I'd probably have a "SortOrder" column for future use.Then you can JOIN the tables and order by the SortOrder.

No need repeat the CASE in every query that needs it

Edit: Simplifying Andomar's idea...

order by
    case status
       when 'Incomplete' then 1
       when 'In Progress' then 2
       when 'Completed' then 3
    END,
    case severity
       when 'High' then 1
       when 'Moderate' then 2
       when 'Low' then 3
    END

You can store Status and Severity as numbers, the use:

SELECT ... FROM ... ... ORDER BY STATUS [ASC/DESC], SEVERITY [ASC/DESC]

ASC or DESC will depend on wich numbers you choose.

To have severity and status as text you can use a IF/Switch or a INNER JOIN if you want more Status/Severity