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
select ... from ... order by case when color = 'red' then 1 when color = 'orange' then 2 when ... end
. Not sure why you have two id's per color in your example though.case
?