Case Statement with Order by

Question

Hi,

Is there any way to order by case statement below please? The output I get almost works, but the the exception of 10 (example shown below).

select
distinct

case
when t_table = '1' and t_field = '1' and t_text = 'Test1' then cast(1 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test2' then cast(2 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test3' then cast(3 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test4' then cast(4 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test5' then cast(5 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test6' then cast(6 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test7' then cast(7 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test8' then cast(8 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test9' then cast(9 as varchar(max))
when t_table = '1' and t_field = '1' and t_text = 'Test10' then cast(10 as varchar(max))
else ' ' end as 'ID'

from xxxxxxxxxxxxxxx

My output for ID is as follows

1
10
2
3
4
5
6
7
8
9

Ideally I would like it to be 1 - 10.

Answer 1

I think that a quick solution is something like this:

 select ... from ...
 order by cast(ID as int)

The rows that contain ' ' will precede other rows.

However, it is probably not the most performant approach in case of large data.

Answer 2

Good day @Bone_12

In such cases where you have a query which return the result set you need but you want to do another step of processing you can use Common Table Expression (CTE).

You can simply wrap your entire query into a CTE section and treat it as virtual table which you can query. In the outside query you can use simple SELECT from the CTE and add ORDER BY

You can read more about using CTE in this doc:
https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

If you will provide queries to create your table and insert some sample data + your current query then we could show you how this is done in your specific case

In general it should look like this

;With MyCTE as ( <enter your query here and make sure all columns has explicit names and no use of ORDER BY here> )  
SELECT * FROM MyCTE  
ORDER BY ID  

Answer 3

Just to be clear.

You are trying to sort by a string "1", "10", "2",... The string "10" will always sort after "1" and before "2". If you want a numeric sort, you need to sort by a number type.

Answer 4

Just remove all these cast(1 as varchar(MAX)) and replace it by bare number. And remove the ELSE '' to get NULL where there is no match.

Answer 5

Hi @Bone_12 ，

When your id contains a null value, it will be converted to 0.If we do not convert id to INT type and sort by the VARCHAR type in your code, you may not get the result you expect.

Please try:

select  
distinct  
cast(case  
when t_table = '1' and t_field = '1' and t_text = 'Test1' then cast(1 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test2' then cast(2 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test3' then cast(3 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test4' then cast(4 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test5' then cast(5 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test6' then cast(6 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test7' then cast(7 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test8' then cast(8 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test9' then cast(9 as varchar(max))  
when t_table = '1' and t_field = '1' and t_text = 'Test10' then cast(10 as varchar(max))  
else ' ' end as int) ID  
from xxxxxxxxxxxxxxx  
order by ID  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.