T-SQL Tutorial

Msg 104 Level 16 - Order By items must appear in the select list


Transact SQL language the Msg 104 Level 16 - ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Msg 104 Level 16 Example

Invalid select

select id, name, job
from EMPLOYEES
where dept_id=10
union
select id, name, job
from EMPLOYEES
where dept_id=20
order by sal desc;

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




Correct select

select id, name, job, sal
from EMPLOYEES
where dept_id=10
union
select id, name, job, sal
from EMPLOYEES
where dept_id=20
order by sal desc;

Other error messages: