How to return rows with a specific value last?
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;