Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 14 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
add comment

Your Answer

 
discard

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.