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?

2 Answers 2


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.

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)

  • Note that a) the brackets are optional: a case expression (as this is called) is valid anywhere a column can be specified; and b) the expression can be slightly simplified to case `cat` when 'mgr' then 1 when 'dev' then 2 else 3 end Commented Nov 19, 2014 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.