question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked EchoLiu-msft commented

Case Statement with Order by

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.



sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach commented

Good day @Bone12-2270

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://docs.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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ops... The above is not relevant :-)

I noticed the rest of the answers and this is true. You simply use string and not numbers so it is sorted as string... @Viorel-1 solution should work

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @Bone12-2270

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.