Case Statement with Order by

Bone_12 361 Reputation points
2021-09-09T18:21:06.287+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,136 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Viorel 114K Reputation points
    2021-09-09T18:42:36.247+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Ronen Ariely 15,091 Reputation points
    2021-09-09T18:37:55.287+00:00

    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  
    

  3. Tom Phillips 17,721 Reputation points
    2021-09-09T19:03:32.37+00:00

    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.

    0 comments No comments

  4. Erland Sommarskog 104.3K Reputation points MVP
    2021-09-09T22:07:40.567+00:00

    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.

    0 comments No comments

  5. EchoLiu-MSFT 14,576 Reputation points
    2021-09-10T02:10:45.967+00:00

    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.

    0 comments No comments