TSQL - Is it possible to define the sort order?

sql sql-server tsql sql-server-2000 sql-order-by

Justin808 · Jan 25, 2011 · Viewed 28k times · Source

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

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.