Order by calculated column with alias inside case expression

Question

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?

The alias is being evaluated before it's available: ["Invalid Column Name" error in query with order by case statement and outer select](https://stackoverflow.com/questions/18277782/invalid-column-name-error-in-query-with-order-by-case-statement-and-outer-sele) Use a derived table. https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8889c5479abf4c11e7d14e6b9468bcc6 — SOS, Apr 22 '22 at 04:21
@DaleK - The derived table is mentioned in the comments and the fiddle above, so I'm sticking with "dupe" :-) https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8889c5479abf4c11e7d14e6b9468bcc6 — SOS, Apr 22 '22 at 04:42
I BELIEVE you can order by the ORDINAL COLUMN reference, such as order by 4 — DRapp, Apr 22 '22 at 23:50
@drapp ordering by ordinal value is recommended against. And as far as I know if suffers that same issue as asked i.e. you can't combine it with other logic — Dale K, Apr 23 '22 at 02:27

Dale K · Accepted Answer · 2022-04-22T23:11:58.540

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.

Or you could just wrap it in `(` and `)` and make it a derived table, so the alias is available in the order by clause. — SOS, Apr 22 '22 at 04:24
Yep. Since the execution plan for cross apply's is often more expensive than alternatives, I prefer to avoid it when there are other readable alternatives. — SOS, Apr 22 '22 at 04:41
It is, and results vary a lot depending on the query and volume. For literals, it may make no difference, but [here's one](https://stackoverflow.com/questions/71714923/display-percentage-of-registered-members-that-have-not-rated-a-movie/71722134#71722134) where cross apply is more expensive than at first glance. Agreed a better dupe probably exists, but I'm too sleepy to find one right now! — SOS, Apr 22 '22 at 04:48
I'm using cross apply and it works perfectly. Thank you very much! — mtdot, Apr 26 '22 at 10:05

Order by calculated column with alias inside case expression

1 Answers1