ORDER BY using custom priorities for text columns

Question

This is a sample table:

name | cat
----------
hugo | sal
bob  | mgr
mike | dev
jeff | mgr
john | dev

Both name and cat being Varchar.

cat reflects a category for these names, but I like to assign a priority to them, followed by a query to list them all, ordered by this priority.

What ways are possible? I could extract cat into a second table, build a foreign key etc. But given my app is very simple and the structure might be non-changeable: What possibilities do I have to list the mgr names first, followed by the dev names, followed by the sal names?

Answer 1

Assuming you can't change your structure, you can use a CASE statement in your ORDER BY:

ORDER BY case cat when 'mgr' then 1 when 'dev' then 2 else 3

If you can change your structure, however, you can create a Category table including the category code and a ranking, join your sample table to the Category table and sort by this ranking.

Answer 2

ORDER BY (case when `cat` = 'mgr' then 1 
when `cat` = 'dev' then 2 
else 3 end)

This structure help me in Wordpress. For example to select custom posts first theh other: poster and project is custom SELECT post_type FROM wp_posts ORDER BY (case when post_type = 'poster' then 1 when post_type = 'project' then 2 else 3 end)