4

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'
5

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; 
1

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
  • The ORDER BY SortOrder, CategoryDescription will provide the order of the first query as well as the overall ordering desired. – Dan Guzman Nov 10 '16 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.