Order by with case and Union in Sql Server

Question

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.

Answer 1

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

Answer 2

Do what it tells you to do to fix it:

SELECT Col_a, Col_b, 0 AS Col_c,
    CASE WHEN Col_a='Other' THEN 1 ELSE 0 END as p
FROM table1
WHERE confitions 

UNION

SELECT Col_a, NULL AS Col_b, Col_c,
     CASE WHEN Col_a='Other' THEN 1 ELSE 0 END
FROM table2
WHERE confitions 

ORDER p , Col_a