SQL: Custom Sort Order
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