Using CASE to Sort ENUM Values in MySQL

There is a tasks table. It has a status column of type enum(todo, inprogress, done)

SELECT * FROM tasks;
-- +----+----------------------+------------+
-- | id | name                 | status     |
-- +----+----------------------+------------+
-- |  1 | Write blog post      | todo       |
-- |  2 | Fix bug #342         | inprogress |
-- |  3 | Design homepage      | done       |
-- |  4 | Update documentation | todo       |
-- |  5 | Deploy to staging    | inprogress |
-- |  6 | Plan sprint meeting  | done       |
-- |  7 | Refactor codebase    | todo       |
-- |  8 | Test new features    | inprogress |
-- |  9 | Clean up database    | done       |
-- | 10 | Create wireframes    | todo       |
-- +----+----------------------+------------+
-- 10 rows in set (0.01 sec)

Suppose we want to select all the tasks sorted by the status — inprogress comes first, todo second, and done last.

If we use ORDER BY status, the tasks will be sorted based on the index in the enum definition. So the result will be todo comes first, inprogress second, and done last. For example:

SELECT * FROM tasks
ORDER BY status;
-- +----+----------------------+------------+
-- | id | name                 | status     |
-- +----+----------------------+------------+
-- |  1 | Write blog post      | todo       |
-- |  4 | Update documentation | todo       |
-- |  7 | Refactor codebase    | todo       |
-- | 10 | Create wireframes    | todo       |
-- |  2 | Fix bug #342         | inprogress |
-- |  5 | Deploy to staging    | inprogress |
-- |  8 | Test new features    | inprogress |
-- |  3 | Design homepage      | done       |
-- |  6 | Plan sprint meeting  | done       |
-- |  9 | Clean up database    | done       |
-- +----+----------------------+------------+
-- 10 rows in set (0.00 sec)

To solve this, we can use the CASE clause in ORDER BY. In the CASE clause, each value in the enum becomes a condition and is assigned a number. The smallest number will appear first in the order.

For example:

SELECT * FROM tasks
ORDER BY CASE
    WHEN status = 'inprogress' THEN 1
    WHEN status = 'todo' THEN 2
    ELSE 3
END;
-- +----+----------------------+------------+
-- | id | name                 | status     |
-- +----+----------------------+------------+
-- |  2 | Fix bug #342         | inprogress |
-- |  5 | Deploy to staging    | inprogress |
-- |  8 | Test new features    | inprogress |
-- |  1 | Write blog post      | todo       |
-- |  4 | Update documentation | todo       |
-- |  7 | Refactor codebase    | todo       |
-- | 10 | Create wireframes    | todo       |
-- |  3 | Design homepage      | done       |
-- |  6 | Plan sprint meeting  | done       |
-- |  9 | Clean up database    | done       |
-- +----+----------------------+------------+
-- 10 rows in set (0.00 sec)

As we can see in the result, the tasks are sorted by the status column — inprogress comes first, todo second, and done last.

Here's the syntax to use the CASE clause:

CASE  
    WHEN  condition1  THEN  condition1value  
    WHEN  condition2  THEN  condition2value
    WHEN  conditionN  THEN  conditionNvalue  
    ELSE  otherconditionvalue  
END;

Summary

The example above is how to sort data by enum values in MySQL using the CASE clause. There are other ways to solve this problem — for example, we can use the FIELD function, modify the enum order, and so on.