TSQL - Is it possible to define the sort order?

Question

Is it possible to define a sort order for the returned results?

I would like the sort order to be 'orange' 'apple' 'strawberry' not ascending or descending.

I know ORDER BY can do ASC or DESC but is there a DEFINED('orange', 'apple', 'strawberry') type thing?

This will be running on SQL Server 2000.

Answer 1

It's incredibly clunky, but you can use a CASE statement for ordering:

SELECT * FROM Blah  ORDER BY CASE MyColumn      WHEN 'orange' THEN 1      WHEN 'apple' THEN 2      WHEN 'strawberry' THEN 3      END  

Alternately, you can create a secondary table which contains the sort field and a sort order.

TargetValue  SortOrder orange       1 apple        2 strawberry   3 

And join your table onto this new table.

Answer 2

Use a CASE statement:

ORDER BY CASE your_col            WHEN 'orange' THEN 1            WHEN 'apple' THEN 2            WHEN 'strawberry' THEN 3          END  

Alternate syntax, with an ELSE:

ORDER BY CASE             WHEN your_col = 'orange' THEN 1            WHEN your_col = 'apple' THEN 2            WHEN your_col = 'strawberry' THEN 3            ELSE 4          END