Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
You can't order by column ordinal inside an expression either (ORDER BY 1) and your example query can't produce foo = 2 so I made a few guesses about the intent. –  Martin Smith Jul 30 '13 at 21:32
add comment

1 Answer

up vote 8 down vote accepted

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;
share|improve this answer
1  
+1 great, concise explanation –  Aaron Bertrand Jul 30 '13 at 21:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.