DEV Community

Ibrahim
Ibrahim

Posted on

Using CASE to Sort ENUM Values in MySQL

#mysql #sql #database #data

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)