Non-Alphabetic ORDER BY - SQL Server Forums

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

SQL Server Development (2000)

Non-Alphabetic ORDER BY

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-02-22 : 03:35:23

I want to order by a column that can only have the following values:
Estimate, Request, Reccomend, Approved
in that order.
Is a case statement the most efficient way to do this or is there a quicker way?

SELECT Budget, Phase,
case
when Phase = 'Estimate' then 1
when Phase = 'Request' then 2
when Phase = 'Reccomend' then 3
when Phase = 'Approved' then 4
end
as PhaseID
FROM EvalBudget
ORDER BY phaseID, FinYear;

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-22 : 06:54:11

That's probably the best way. You could also put the CASE statement in the ORDER BY clause by itself, if you don't want to have the PhaseID in your column output:

SELECT Budget, Phase
FROM EvalBudget
ORDER BY
case
when Phase = 'Estimate' then 1
when Phase = 'Request' then 2
when Phase = 'Reccomend' then 3
when Phase = 'Approved' then 4
end, FinYear;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources