CODEWITHSUNDEEP
sqlsql-server
user1451111
user1451111

Reputation: 1963

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

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

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'

Upvotes: 0

Views: 606

Answers (3)

bastos.sergio
bastos.sergio

Reputation: 6764

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

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You may looking for this

select 'T' as Success,  code_id as CategoryID, code_desc as CategoryDescription
from codes
where (code_id > '000003' and code_id < '000100') OR 
      (code_id > '000001' and code_id < '000004') 
ORDER BY CASE
           WHEN (code_id > '000003' and code_id < '000100') THEN 1
           WHEN (code_id > '000001' and code_id < '000004') THEN 2
         END

Upvotes: 0

KyLim
KyLim

Reputation: 476

select 'T' as Success,  code_id as CategoryID, code_desc as CategoryDescription
from codes
where (code_id > '000003' and code_id < '000100') or (code_id > '000001' and code_id < '000004')

order by code_id desc

your code is too long why dont you change it to this?

Upvotes: 0

Related Questions