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

In SQL Server, you may face an error and get the below error message:

Msg 104, Level 16, State 1, Line 4
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Below is the solution of your query, make sure you have followed the pattern as below

Select * from
(               
            SELECT   field1, field2 FROM Table1 WHERE  field1 = 'abc'
            UNION
            SELECT   field1, field2 FROM Table2 WHERE  field2 = 'xyz'
) VarTable
                    order by case when field1 is not null then field1 else field2 end desc

Let us know your feedback.

Thank you Hiral Shah for this post!

By AlpesH Shah