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

Add a key to the table (e.g. fruit_id int identity(1,1) primary key) to preserve the order of insert

create table fruit(fruit_id int identity(1,1) primary key, name varchar(50))
go

insert into fruit(name) values ('orange')
insert into fruit(name) values ('apple')
insert into fruit(name) values ('strawberry')

select name from fruit

result:

orange
apple
strawberry