Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 20 down vote accepted

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.

share|improve this answer
That's exactly it. Thanks! – Sebastian Roth Jan 4 '11 at 3:17
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)

share|improve this answer
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 – Colin 't Hart Nov 19 '14 at 9:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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