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.

Pathogen

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.

Aerostatics

Hah! Like I'm going to trust any SQL coming from someone called LittleBobbyTables :-) – Pennate

@pax - <insert picture of Admiral Ackbar here> :-) – Aerostatics

worked like a charm, I wish I could add 100 upvotes. – Biweekly

What if i want certain items to appear on the end of the SELECT results ? – Raynaraynah

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 

Interpretative

What if i want certain items to appear on the end of the SELECT results ? – Raynaraynah

Answer 3

If this is going to be a short-lived requirement, use a case statement. However, if you think it may be around for a while, and it's always going to be orange/apple/strawberry order (or even if not - see below), you may want to think about sacrificing some disk space to gain some speed.

Create a new column in your table called or_ap_st and use an insert/update trigger to populate it with the number 1, 2 or 3, depending on the the value of your fruit column. Then index on it.

Since the only time the data in that column will change is when the row changes, that's the best time to do it. The cost will then be incurred on a small number of writes rather than a large number of reads, hence amortised over the select statements.

Your query will then be a blindingly fast:

select field1, field2 from table1
order by or_ap_st;

with no per-row functions killing the performance.

And, if you want other sort orders as well, well, that's why I called the column or_ap_st. You can add as many other sorting columns as you need.

Pennate

Answer 4

What I do in that case is

ORDER BY
  CASE WHEN FRUIT = 'Orange' THEN 'A' 
       WHEN FRUIT = 'Apple' THEN 'B'
       WHEN FRUIT = 'Strawberry' THEN 'C'
       ELSE FRUIT
END

Scoggins

What if i want certain items to appear on the end of the SELECT results ? – Raynaraynah

you can use a very high number or Z – Galan

Answer 5

Going further from turtlepick's answer:

ORDER BY
  CASE WHEN FRUIT = 'Orange' THEN 'A' 
       WHEN FRUIT = 'Apple' THEN 'B'
       WHEN FRUIT = 'Strawberry' THEN 'C'
       ELSE FRUIT
  END

In case you have some more items in FRUIT and they happen to start with letters defined after THEN keywords, those items would appear within the hardcoded order. For example Banana shows up before Strawberry. You can circumvent it with

ORDER BY
  CASE
    WHEN FRUIT = 'Orange' THEN '.1'
    WHEN FRUIT = 'Apple' THEN '.2'
    WHEN FRUIT = 'Strawberry' THEN '.3'
    ELSE FRUIT
  END

Here I have used characters with lower ASCII values in hope that they would not appear at the beginning of values in FRUIT.

Szymanski

Answer 6

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

Edi