Sql-server – ORDER BY items must appear in the select list […]

caseorder-bysql serversql-server-2008-r2

Using Microsoft SQL Server 2008, I get the following error.

Msg 104, Level 16, State 1, Line 43

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The query is I am using is kind of complex, but the CASE statement in side of the ORDER BY clause can not see the aliased column name, here is a brief example.

SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
ORDER BY CASE WHEN foo = 2 THEN 1 END;

In my production query the left-query needs to be ordered by the column [360_set] found in the table, and the right-query needs to be ordered as if [360_set] was null.

How do I fix this error, and why does this syntax generate an error?

Here is the version info,

Microsoft SQL Server Management Studio     10.0.5512.0
Microsoft Analysis Services Client Tools   10.0.5500.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML                            3.0 6.0 
Microsoft Internet Explorer                9.10.9200.16635
Microsoft .NET Framework                   2.0.50727.5472
Operating System                           6.1.7601

Best Answer

If an alias is used in an ORDER BY it must be used on its own, not inside an expression.

If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.

So for example

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B

Returns (ordered by alias)

+---+
| B |
+---+
| 1 |
| 2 |
| 3 |
+---+

But

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B + 0

Returns (Ordered by base table column B)

+---+
| B |
+---+
| 3 |
| 2 |
| 1 |
+---+

You can of course just wrap the whole thing in a derived table or CTE.

WITH T AS
(
SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
)
SELECT *
FROM T
ORDER BY CASE WHEN foo = 1 THEN bar END;
Related Question