Ordering Results Non-Alphabetically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Ordering Results Non-Alphabetically

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-06 : 08:28:38

Avi writes "I have a field that could contain one of the following four entries:

Master
Senior
Mid-Level
Junior

I can use the following SQL statement to get the results:

SELECT * FROM Skill ORDER BY SkillLevel

This would order the results alphabetically. However, I would like the results ordered by the SkillLevel field based on the order listed above. How can this be accomplished?

Thanks for your response,
Avi"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-06 : 08:27:28

SELECT * FROM Skill
ORDER BY CASE SkillLevel
WHEN 'Master' THEN 1
WHEN 'Senior' THEN 2
WHEN 'Mid-Level' THEN 3
WHEN 'Junior' THEN 4
ELSE 5 END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-06 : 09:16:55

Create a table with these values (if you don't have that alredy) and add a sort column. Let the data drive your queries, whenever possible. Try to avoid hard-coding in case statements unless you have no other choice (i.e., you cannot add tables or columns to the DB). In that case, what Rob showed you works perfecctly.

In general, just remember that if you hard-code data in your SQL, you will a) need to do it everywhere this logic needs to exist and b) you will need to maintain this whenever new values become available, or existing values change, or the sort order changes. Keep the data in your database tables and out of your SQL statements whenever you can.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-06 : 20:10:40

Yeah, I shoulda said that in the first place.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-06 : 22:00:44

Rob -- I have a better one than yours for this case only:

order by substring(SkillLevel,2,1)

!!!
a-e-i-u -- works perfectly !!

(please don't do this, though .... )

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-06 : 22:05:23

ROFL!

See, I should have noticed THAT at first too!

There is one flaw though: if they had a "Moron" skill level it would not sort right using that logic.

Unless of course it's a management position...what am I saying, "unless"?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources