Order by calculated column with alias inside case expression-CodePudding

I've got a problem with my order by clause when using a calculated column with an alias as below:

This order by works without any problem

declare @Mode int = 1
declare @Sort nvarchar(max) = 'engname'

select top 10 School.Id          as EntityId,
              School.EnglishName as EntityEnglishName,
              School.Name        as EntityNativeName,
              case
                  when @Mode = 0 then 0
                  when @Mode = 1 then 1
                  when @Mode = 2 then 2
                  end as ActiveStudents
from V_SchoolMinimized as School
Order By ActiveStudents

The following query has an error:

Invalid column name 'ActiveStudents'

declare @Mode int = 1
declare @Sort nvarchar(max) = 'engname'

select top 10 School.Id          as EntityId,
       School.EnglishName as EntityEnglishName,
       School.Name        as EntityNativeName,
       case
           when @Mode = 0 then 0
           when @Mode = 1 then 1
           when @Mode = 2 then 2
           end as ActiveStudents
from V_SchoolMinimized as School
Order By
    case when @Sort is null then School.Id end,
    case when @Sort = 'engname' then ActiveStudents end

How can I use ActiveStudents within the conditional order by clause as shown?

CodePudding user response：

So while you can use a calculated column in your ORDER BY clause (but not in other clauses such as GROUP BY), you cannot then apply further calculations or conditions - it must be used exactly as created.

There are a whole bunch of ways to solve this problem. Which approach you use will come down to some combination of:

Which option is clearer to you as the developer
Which option performs better
Which option fits into your existing query better

Option 1: Repeat the logic

I don't recommend this option because it violates the DRY principle thereby making it harder to maintain and easier to make mistakes.

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , case
          when @Mode = 0 then 0
          when @Mode = 1 then 1
          when @Mode = 2 then 2
      end as ActiveStudents
from V_SchoolMinimized as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
        end
    end;

The rest of the options are sub-query variations the choice of which comes down to the comments provided as the start.

Option 2: Use a derived table sub-query

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
) as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

Option 3: Use a CTE (Common Table Expression)

with cte as (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
)
select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from cte
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

Option 4: Use CROSS APPLY

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , A.Students
from V_SchoolMinimized as S
cross apply (
    values (
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
            end
    )
) as A (Students)
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then A.Students end;

Note: I suggest keeping your table aliases nice and short, 1-2 characters where possible, occasionally 3.