Order by with case and Union in Sql Server

sql-order-by, sql-server, union

Solution

Use an inner query:

SELECT * FROM (
    SELECT Col_a, Col_b, 0 AS Col_c FROM table1
    WHERE conditions 

    UNION 

    SELECT Col_a, NULL AS Col_b, Col_c FROM table2
    WHERE conditions 
) x
ORDER BY CASE WHEN Col_a='Other' THEN 1 ELSE 0 END, Col_a

Problem

i have a query similar to this, ``` SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE confitions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE confitions ORDER BY CASE WHEN Col_a='Other' THEN 1 ELSE 0 END , Col_a ``` but when i run this query it gives me an error: `ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. Severity 16` and if i remove the `case` statement it works fine, can any one help me to solve this issue, thanks in advance.

Original source