How to return rows with a specific value last?

Question

My question is reverse of this question, is it possible to define the sort order.

I want to return some records from SQL Server database, sorted in ascending order based on a column's value BUT two particular records should always be on the end.

The original SQL (which needs to be changed) is below:

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000001' and code_id < '000100'
order by CategoryDescription

The results are returned as

• 000003 ***First Record
• 000002 ***Another Record
• 000004 Apple
• 000016 Books
• 000014 Cables

I want the below result (first two with asteriks on the last):

• 000004 Apple
• 000016 Books
• 000014 Cables
• 000003 ***First Record
• 000002 ***Another Record

I tried the below UNION statement but the resultset is automatically sorted in ascending order and those two rows are in the beginning by default.

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000003' and code_id < '000100' 
--order by categoryDescription

UNION

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000001' and code_id < '000004'

Answer 1

This seems like a much simpler solution than introducing a bunch of unions:

SELECT code_id as CategoryID, code_desc as CategoryDescription
FROM dbo.codes
WHERE code_id > '000001' AND code_id < '000100'
ORDER BY CASE code_id
  WHEN '000002' THEN 3
  WHEN '000003' THEN 2 
  ELSE 1 END, CategoryDescription; 

Answer 2

Try something like this:

Select CategoryID, CategoryDescription from
(
select 1 as SortOrder, code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000003' and code_id < '000100' 
--order by categoryDescription

UNION ALL

select 2 as SortOrder, code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id = '000003'

UNION ALL

select 3 as SortOrder, code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id = '000002'
) a
order by SortOrder  --and whatever else