Must ORDER BY items appear in the select list if the statement contains a UNION operator?

Question

A sql script , It have run with no problem in production server for long time, But lately system report "ORDER BY items Must appear in the select list if the statement contains a UNION operator "

It is strange that the sql script still can run in my test server. So I don't know if any revision that I do can run correctly in production.

SELECT '' as value ,'Outstanding' as text , 0 as disp_order
union
select a.buyer_status_code AS value , a.buyer_status_name AS text ,a.disp_order
FROM   rfq_buyer_status_v a WITH (NOLOCK)
ORDER BY a.disp_order

Answer 1

try this:

select * from 
(
SELECT '' as value ,'Outstanding' as text , 0 as disp_order
union
select a.buyer_status_code AS value , a.buyer_status_name AS text ,a.disp_order
FROM   rfq_buyer_status_v a WITH (NOLOCK)
) t

ORDER BY t.disp_order

Answer 2

SQL server parses the query in order of the phases as following:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

Each phase operates on one or more tables as inputs and returns a virtual table as output. The output table of one phase is considered the input to the next phase. This is in accord with operations on relations that yield a relation. If an ORDER BY is specified, the result is NOT relational.

Any alias defined in an earlier phase can be seen by phases following it, but not vice versa.

e.g.

following query is okay

SELECT [Col_1], [Col_2] 
FROM Table_A AS A 
WHERE A.[Col_1] > 0
ORDER BY A.[Col_2]; 

while this one is not going to work

SELECT [Col_1] AS C1, [Col_2] AS C2 
FROM Table_A 
WHERE C1 > 0
ORDER BY C2; 

Now, the UNION set operator unifies the results of the two input queries. As a set operator, UNION has an implied DISTINCT property, meaning that it does not return duplicate rows.

If you used UNION in your query, it combined the two sets into one and the ORDER BY is going to take effect on the combined set, not only on the SELECT ... FROM ... dataset. The reason is that ORDER BY is the weakest (or last one) in the queue and it 'Catches All'

In first line of your query, you have give 0 an alias called 'disp_order', that is fine. after the UNION operator, you queried a dataset from table 'rfq_buyer_status_v' which includes a column named 'disp_order', that is quite acceptable as well. The problem comes out from after the ORDER BY.

As you have also given the table an alias, namely 'a', ORDER BY a.disp_order means restrict the ordering action on partial of the combined dataset, which conflicts with the 'Catches All' activity.

The solution would be remove the 'a.' after the ORDER BY clause. The reason you could do it in this way is that you gave 0 the alias 'disp_order' which is the same of the column name from table rfq_buyer_status_v. Well in this case i couldn't see any value of aliasing the table ...

SELECT '' AS value ,'Outstanding' AS text , 0 AS disp_order
UNION 
SELECT a.buyer_status_code AS value , a.buyer_status_name AS text ,a.disp_order
FROM   rfq_buyer_status_v a WITH (NOLOCK)
ORDER BY disp_order

An alternative way of writing the query is

SELECT ''            AS value ,
       'Outstanding' AS text  , 
       0             AS ABC
UNION 
SELECT buyer_status_code AS value , 
       buyer_status_name AS text ,
       disp_order        AS ABC
FROM   rfq_buyer_status_v 
    WITH (NOLOCK)
ORDER BY ABC

The second gave the disp_order column a totally different alias and made it easy to read and reduced the chances of getting confused when reviewing the code.