Why Can't I Refer to a Column Alias in the Order by Using Case

Why can't i refer to a column alias in the ORDER BY using CASE?

This has to do with how a SQL dbms resolves ambiguous names.

I haven't yet tracked down this behavior in the SQL standards, but it seems to be consistent across platforms. Here's what's happening.

create table test (
  col_1 integer,
  col_2 integer
);

insert into test (col_1, col_2) values 
(1, 3), 
(2, 2), 
(3, 1);

Alias "col_1" as "col_2", and use the alias in the ORDER BY clause. The dbms resolves "col_2" in the ORDER BY as an alias for "col_1", and sorts by the values in "test"."col_1".

select col_1 as col_2
from test
order by col_2;

col_2
--
1
2
3

Again, alias "col_1" as "col_2", but use an expression in the ORDER BY clause. The dbms resolves "col_2" not as an alias for "col_1", but as the column "test"."col_2". It sorts by the values in "test"."col_2".

select col_1 as col_2
from test
order by (col_2 || '');

col_2
--
3
2
1

So in your case, your query fails because the dbms wants to resolve "NewValue" in the expression as a column name in a base table. But it's not; it's a column alias.

PostgreSQL

This behavior is documented in PostgreSQL in the section Sorting Rows. Their stated rationale is to reduce ambiguity.

Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong

This restriction is made to reduce ambiguity. There is still ambiguity if an ORDER BY item is a simple name that could match either an output column name or a column from the table expression. The output column is used in such cases. This would only cause confusion if you use AS to rename an output column to match some other table column's name.

Documentation error in SQL Server 2008

A slightly different issue with respect to aliases in the ORDER BY clause.

If column names are aliased in the SELECT list, only the alias name can be used in the ORDER BY clause.

Unless I'm insufficiently caffeinated, that's not true at all. This statement sorts by "test"."col_1" in both SQL Server 2008 and SQL Server 2012.

select col_1 as col_2
from test
order by col_1;

Unable to ORDER BY on Column Alias when using CASE

Okay, here are some comments that I have.

1 - Please get away from old style joins. Use the INNER JOIN ON clause.

2 - There is no reason why an alias can not be used in the ORDER BY clause. Please see Itzik Ben-Gans posting on logical processing order. The SELECT arguments are processed way before the ORDER BY.

http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

3 - Last but not least, a simple example (adventureworks) that make everyone with a hire date less than 2004 as Active, everyone else is in-active. This will sort by the status column.

Good luck.

John

-- Sample database
Use AdventureWorks2012
GO

-- Sample select showing alias works fine in a order by clause.
SELECT [LoginID] as login_id,
    CASE WHEN (e.HireDate < '20040101') THEN 'Active' 
    ELSE 'InActive' END AS emp_status
FROM [HumanResources].[Employee] as e
ORDER BY emp_status desc
GO

Since you changed your code above, here is a new answer to match. For a CASE statement on an ORDER BY you have to use the actual columns. For just a simple ORDER BY, the alias will work.

SO THE ANSWER is it ALL DEPENDS!!

Use AdventureWorks2012
GO

ALTER PROCEDURE usp_Sort_By_Column(@sort varchar(25))
AS
SELECT 
    [LoginID] as login_id,
    CASE WHEN (e.HireDate < '20040101') 
    THEN 'Active' ELSE 'InActive' END AS emp_status
FROM 
    [HumanResources].[Employee] as e
ORDER BY 
   (CASE 
       WHEN @sort = 'ID' THEN [LoginID] ELSE 
        (CASE WHEN (e.HireDate < '20040101') 
        THEN 'Active' ELSE 'InActive' END)
     END) 
GO

usp_Sort_By_Column 'STATUS'

Link to ORDER BY - Books On Line ...

http://msdn.microsoft.com/en-us/library/ms188385.aspx

Best answer for your crazy query: Make a sort column that is dynamic using the variable. Just order by the first column. Cleanest answer.

Use AdventureWorks2012
GO

ALTER PROCEDURE usp_Sort_By_Column(@sort varchar(25))
AS
SELECT 
    (CASE 
       WHEN @sort = 'ID' THEN [LoginID] ELSE 
        (CASE WHEN (e.HireDate < '20040101') 
        THEN 'Active' ELSE 'InActive' END)
     END) as Sort_Column,

    [LoginID] as login_id,
    CASE WHEN (e.HireDate < '20040101') 
    THEN 'Active' ELSE 'InActive' END AS emp_status
FROM 
    [HumanResources].[Employee] as e
ORDER BY 
   1
GO

usp_Sort_By_Column 'ID'

ORDER BY with a CASE statement for column with alias

Try this

ORDER BY 
    CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'ASC'
        THEN MAX(PayCheckDate) END ASC,
    CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'DESC'
        THEN MAX(PayCheckDate) END DESC

Example

create table Test (id int, somevalue int)

insert Test values(1,1)
insert Test values(2,1)
insert Test values(3,2)
insert Test values(3,2)
insert Test values(4,2)

run this in 1 shot

declare @sortDirection char(4)
select  @sortDirection = 'DESC'

select somevalue, COUNT(*)
 from Test
 group by somevalue 
 order by case when @sortDirection = 'ASC'
 then COUNT(*) end asc,
 case when @sortDirection = 'DESC'
 then COUNT(*) end desc

 select  @sortDirection = 'ASC'
 select somevalue, COUNT(*)
 from Test
 group by somevalue 
 order by case when @sortDirection = 'ASC'
 then COUNT(*) end asc,
 case when @sortDirection = 'DESC'
 then COUNT(*) end desc

MSSQL CASE after ORDER BY not working with alias

You cannot use aliased columns in the ORDER BY clause because sorting logically it happens before the SELECT clause. You could use column numbers, e.g. ORDER BY 9 would sort the output by your 'Liefertermin' column, but that is considered a bad practice. Best way around it would be to put everything inside a subquery, like:

SELECT * FROM
(SELECT... -- your whole select here without the ORDER BY clause
 ) as t1
ORDER BY
    CASE WHEN @Sort = 1 then SortDate END DESC
    CASE WHEN @Sort = 2 then Liefertermin END DESC
    CASE WHEN @Sort = 3 then Name END DESC

Why can't I use column aliases in the next SELECT expression?

You can use a previously created alias in the GROUP BY or HAVING statement but not in a SELECT or WHERE statement. This is because the program processes all of the SELECT statement at the same time and doesn't know the alias' value yet.

The solution is to encapsulate the query in a subquery and then the alias is available outside.

SELECT stddev_time, max_time, avg_time, min_time, cnt, 
       ROUND(avg_time * cnt, 2) as slowdown
FROM (
        SELECT 
            COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
            MAX(time) as max_time, 
            ROUND(AVG(time), 2) as avg_time, 
            MIN(time) as min_time, 
            COUNT(path) as cnt, 
            path
        FROM 
            loadtime
        GROUP BY
            path
        ORDER BY
            avg_time DESC
        LIMIT 10
   ) X;

SQL: Alias Column Name for Use in CASE Statement

I think that MySql and MsSql won't allow this because they will try to find all columns in the CASE clause as columns of the tables in the WHERE clause.

I don't know what DBMS you are talking about, but I guess you could do something like this in any DBMS:

SELECT *, CASE WHEN a = 'test' THEN 'yes' END as value FROM (
   SELECT col1 as a FROM table
) q

Order by calculated column with alias inside case expression

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.

Can not order by alias in SQL Server 2012

Yes, you can use it in order by, but no in the case statement.

CREATE VIEW NAME
AS
SELECT BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM AS USER_FULL_NAME --, next cols
FROM CAORAC CAORAC INNER JOIN CAACCO CAACCO
    ON CAORAC.ACCO_KEY = CAACCO.ACCO_KEY
    INNER JOIN BAUSER BAUSER
    ON CAORAC.USER_KEY = BAUSER.USER_KEY

SELECT              
    USER_FULL_NAME AS USER_FULL_NAME,
    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME ASC' THEN USER_FULL_NAME END USER_FULL_NAME_ASC ,
    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME DESC' THEN USER_FULL_NAME END USER_FULL_NAME_DESC
FROM NAME
ORDER 
    USER_FULL_NAME_ASC ASC,
    USER_FULL_NAME_DESC DESC

Or you can add case into your view too. It depends on puropse but it could be candidate for table value function. Or if you prefer more complex qry you can use derived table (instead of view).

SQL not recognizing column alias in where clause

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING
clauses to refer to the column.

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is
evaluated, the column value may not yet have been determined.

So, the following query is illegal:

SQL> SELECT empno AS employee, deptno AS department, sal AS salary
  2  FROM emp
  3  WHERE employee = 7369;
WHERE employee = 7369
      *
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier

SQL>

The column alias is allowed in:

• GROUP BY
• ORDER BY
• HAVING

You could refer to the column alias in WHERE clause in the following cases:

1. Sub-query
2. Common Table Expression(CTE)

For example,

SQL> SELECT * FROM
  2  (
  3  SELECT empno AS employee, deptno AS department, sal AS salary
  4  FROM emp
  5  )
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL> WITH DATA AS(
  2  SELECT empno AS employee, deptno AS department, sal AS salary
  3  FROM emp
  4  )
  5  SELECT * FROM DATA
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL>

Referring to a Column Alias in a WHERE Clause

SELECT
   logcount, logUserID, maxlogtm,
   DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

But, as mentioned in other answers, you can force SQL to treat SELECT to be handled before the WHERE clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):

Parenthesis/Subselect:

SELECT
   *
FROM
(
   SELECT
      logcount, logUserID, maxlogtm,
      DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary   
) as innerTable
WHERE daysdiff > 120

Or see Adam's answer for a CTE version of the same.

---

Related Topics