SQL: Custom Sort Order Suppose I have a column with colour names (colour). Naturally, it will be a character field.

I would like to sort the table by colour, but not in alphabetical order, but by rainbow or transistor colour code order.

What is the simplest way to sort a column using an arbitrary sort order? Something like this:

id  name        colour
1   this        red
4   that        red
2   stuff       orange
5   nonsense    orange
3   whatever    yellow
6   etc         yellow


where the colours in in rainbow order.

The use of colours is just an example. There are many sets of textual data where the order is not alphabetical.
Use a case statement to establish the order, or add a lookup table with all colours where you can set an order.


declare @t table(id int, name varchar(20), colour varchar(10));
insert into @t values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

select *
from   @t
order by (case when colour = 'yellow' then  1
               when colour = 'orange' then 0
               else 2
           end);
GO


id | name     | colour
-: | :------- | :-----
 2 | stuff    | orange
 5 | nonsense | orange
 3 | whatever | yellow
 6 | etc      | yellow
 1 | this     | red   
 4 | that     | red   


dbfiddle here


create table foo(id int, name varchar(20), colour varchar(10));
insert into foo values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

create table bar(colour varchar(10), [order] int);
insert into bar values
('orange', 0),
('yellow', 1),
('red', 2);



select *
from   foo
inner join bar
on foo.colour = bar.colour
order by bar.[order];


GO


id | name     | colour | colour | order
-: | :------- | :----- | :----- | ----:
 2 | stuff    | orange | orange |     0
 5 | nonsense | orange | orange |     0
 3 | whatever | yellow | yellow |     1
 6 | etc      | yellow | yellow |     1
 1 | this     | red    | red    |     2
 4 | that     | red    | red    |     2


dbfiddle here