How to return rows with a specific value last? 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 &gt; '000001' and code_id &lt; '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 &gt; '000003' and code_id &lt; '000100' 
--order by categoryDescription

UNION

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id &gt; '000001' and code_id &lt; '000004'
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 &gt; '000001' AND code_id &lt; '000100'
ORDER BY CASE code_id
  WHEN '000002' THEN 3
  WHEN '000003' THEN 2 
  ELSE 1 END, CategoryDescription;