user1451111 user1451111 - 27 days ago 21
SQL Question

TSQL - How to return rows with a specific value last?

My question is reverse of this question.

TSQL - 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

If you wish to maintain the union then you could do something like this:

select * from (
    select 'T' as Success,  code_id as CategoryID, code_desc as CategoryDescription, 1 as order
    from codes
    where code_id > '000003' and code_id < '000100' 

    UNION

    select 'T' as Success,  code_id as CategoryID, code_desc as CategoryDescription, 2 as order
    from codes
    where code_id > '000001' and code_id < '000004'
) x
order by x.order