SQL: Custom Sort Order

Question

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.

Answer 1

I would use another table with the key values (also suggested by @McNets) feel its less verbose and more scalable.

-----------------------------
|         Colours           |
-----------------------------
|  KeyValue  |    Name      |
-----------------------------
|     1      |  Red         |
|     2      |  Orange      |
|     3      |  Yellow      |
-----------------------------

-----------------------------
|        OtherTable         |
-----------------------------
| id |  Name    |  Colour   |
-----------------------------
| 1  | this     |   red     |
| 4  | that     |   red     |
| 2  | stuff    |   orange  |
| 5  | nonsense |   orange  |
| 3  | whatever |   yellow  |
| 6  | etc      |   yellow  |
-----------------------------

SELECT
    ot.Id,
    ot.Name,
    ot.Colour
FROM OtherTable ot
INNER JOIN Colours c on c.Name = ot.Colour
ORDER BY c.KeyValue

Answer 2

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