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

Question

Sorry if this a duplicate, but i haven't found one. Why can't i use my column alias defined in the SELECT from the ORDER BY when i use CASE?

Consider this simple query:

SELECT NewValue=CASE WHEN Value IS NULL THEN '<Null-Value>' ELSE Value END
FROM dbo.TableA
ORDER BY CASE WHEN NewValue='<Null-Value>' THEN 1 ELSE 0 END

The result is an error:

Invalid column name 'NewValue'

Here's a sql-fiddle. (Replace the ORDER BY NewValue with the CASE WHEN... that´'s commented out)

I know i can use ORDER BY CASE WHEN Value IS NULL THEN 1 ELSE 0 END like here in this case but actually the query is more complex and i want to keep it as readable as possible. Do i have to use a sub-query or CTE instead, if so why is that so?

Update as Mikael Eriksson has commented any expression in combination with an alias is not allowed. So even this (pointless query) fails for the same reason:

SELECT '' As Empty
FROM dbo.TableA
ORDER BY Empty + ''

Result:

Invalid column name 'Empty'.

So an alias is allowed in an ORDER BY and also an expression but not both. Why, is it too difficult to implement? Since i'm mainly a programmer i think of aliases as variables which could simple be used in an expression.

Why don't you wrap your case condition in outer apply and order by that field? — Andrey Korneyev, Sep 10 '14 at 11:17
@AndyKorneyev: i also know workarounds, but i wondered why this is not allowed. This seems to be quite common and an alias is supposed to hide complexity, so why am i not allowed to do it if i can use aliases in the `ORDER BY` normally? — Tim Schmelter, Sep 10 '14 at 11:18
There's a long discussion here (a bit out of date perhaps): http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4ad14cad-1e61-43a0-96f8-70a506106a00/column-alias-usage-everywhere-in-a-query-following-definition?forum=transactsql — davek, Sep 10 '14 at 11:23
@davek: maybe i've overlooked something, but is this thread not only discussing why we cannot use a column alias everywhere, so even in the `WHERE`? (_"...column alias usage everywhere in the query, not only in the ORDER BY clause..."_) I want to use it only in the `ORDER BY` which should be possible. — Tim Schmelter, Sep 10 '14 at 11:30
@TimSchmelter : Have you read [this](http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/03/why-can-t-we-have-column-alias-in-order-by.aspx) and [this](http://social.msdn.microsoft.com/Forums/sqlserver/en-US/10e920a0-6d85-45ac-af77-b3a1ce58a7d1/column-alias-in-order-by-clause-not-supported-in-sql-server-20052008-causes-enter-migration?forum=transactsql) ? — Arion, Sep 10 '14 at 11:37
@Arion: yes, but the first refers to _table_ aliases in the order by and the second is about a bug in SQL-Server 2000. — Tim Schmelter, Sep 10 '14 at 11:46
I don't think the problem is with the alias. I think it's because of the conditional logic in the order by clause. Let's assume your query returns three records with values of Fred, Barney, and . How should these records be sorted, and why? — Dan Bracuk, Sep 10 '14 at 12:11
@DanBracuk: that's not a problem as i've shown with this sql-fiddle: http://sqlfiddle.com/#!3/0018ac/5/0 This order by should just guarantee that the null-values come last. So it divides all records into two groups: 1) [non-`null`] 2) [`null`] I could add other arguments as well separated by comma, but i wanted to keep it as simple as possible. — Tim Schmelter, Sep 10 '14 at 12:24
It is not only an issue with `CASE`. You can not use a column alias in an expression in the order by, only as a specification of what column you should use. Look at it as a "better" way to specify a sort column than using the integer position. Documentation does not say explicitly that alias is not allowed for expressions but it does not say it is either. `order_by_expression Specifies a column or expression`. And a bit after `A sort column can be specified as a name or column alias, or a nonnegative integer`. So it does say an alias can be used to specify a column. Nothing about expressions. — Mikael Eriksson, Sep 10 '14 at 13:15
@MikaelEriksson: I thought so, i just needed a simple example. So we can use expressions in the `ORDER BY` and we can use aliases there, but we cannot use both. Why, is it too difficult to implement? Since i'm mainly a programmer i think of aliases as variables which could simple be used in an expression. — Tim Schmelter, Sep 10 '14 at 13:25
The fact that in a DISTINCT query you can only sort by columns you are selecting might be of some relevance to your question, if not even be a clue to a degree. — Andriy M, Sep 10 '14 at 15:50

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-09-10T15:39:15.583

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;

Thanks (+). But the question remains: why is that so? Why can i use an expression in the order_by and also an alias, but not both at the same time? The dbms could check if there is an alias `NewValue`, if so take that, otherwise take an available column with that name. Btw, `order by (col_2 + '');` is really strange since it works. It uses the column `col_2` instead of the alias. — Tim Schmelter, Sep 10 '14 at 14:26
I don't yet know why that's so. I expect to find something in the SQL standards, but that takes me a while even under the best of circumstances. — Mike Sherrill 'Cat Recall', Sep 10 '14 at 14:37
Re: Documentation error in SQL Server 2008 – maybe they just failed to put the correct emphasis, maybe they meant to say that if an alias is used to sort the result set, it should be the alias name only (as opposed to an expression using the alias). Pretty wild guess, admittedly. — Andriy M, Sep 10 '14 at 15:43
@AndriyM: You might be right. If you're right, there are clearer ways to say it than you find on that linked page. — Mike Sherrill 'Cat Recall', Sep 10 '14 at 15:55

score 0 · Answer 2 · answered May 24 '21 at 07:07

It seems this limitation is related to another limitation in which "column aliases can't be referenced in same SELECT list". For example, this query:

SELECT Col1 AS ColAlias1 FROM T ORDER BY ColAlias1

Can be translated to:

SELECT Col1 AS ColAlias1 FROM T ORDER BY 1

Which is a legal query. But this query:

SELECT Col1 AS ColAlias1 FROM T ORDER BY ColAlias1 + ' '

Should be translated to:

SELECT Col1 AS ColAlias1, ColAlias1 + ' ' FROM T ORDER BY 2

Which will raise the error:

Unknown column 'ColAlias1' in 'field list'

And finally it seems these are because of SQL standard behaviours not an impossibility in implementation. More info at: Here

Note: The last query can be executed by MS Access without error but will raise the mentioned error with SQL Server.

score -1 · Answer 3 · answered Sep 10 '14 at 12:04

-1

You could try something like:

select NewValue from (
  SELECT (CASE WHEN Value IS NULL THEN '<Null-Value>' ELSE Value END ) as NewValue, 
  ( CASE WHEN NewValue='<Null-Value>' THEN 1 ELSE 0 END) as ValOrder
  FROM dbo.TableA
  GROUP BY Value
) t
ORDER BY ValOrder

answered Sep 10 '14 at 12:04

Chrisky

567
3
9

Thanks. But as i've mentioned i know workarounds like sub-queries or CTE's to expose this column and use it wherever i like(even in the `WHERE`). But the question is why i have to add complexity(which is a sub-query definitely) to do something that [should be allowed in the `ORDER BY`](http://msdn.microsoft.com/en-us/library/ms188385.aspx)(_"**order_by_expression**: Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name **or column alias**, or a nonnegative integer representing the position of the column in the select list"_). – Tim Schmelter Sep 10 '14 at 12:20

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

3 Answers3

Linked

Related