SQL Error 104 : ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

 Msg 104, Level 16, State 1, Line 11

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

This error message appears when you try to sort a resultset by an element that is not contained in the SELECT list and the statement contains a UNION-, INTERSECT-, or EXCEPT operator.

For Example, suppose you are fetching FirstName and LastName columns and you want to generate a list of these names where the first name and last name are concatenated together to form the full name and sort the output by the LastName column:

Use AdventureWorks2017
Go

SELECT [FirstName] +' '+ [LastName] AS [FullName]
FROM Person.Person
where FirstName='Adam'
UNION
SELECT [FirstName] +' '+ [LastName] AS [FullName]
FROM Person.Person
where FirstName='Ben'
ORDER BY [LastName]

Output:

Msg 104, Level 16, State 1, Line 11

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

Since the LastName column is not part of the output, although it’s part of one of the columns in the SELECT list, the above error will be encountered

To avoid this error, simply remove the ORDER BY clause from your SELECT statement. If you really need to sort it by a particular column, just include that column in your SELECT statement.

Use AdventureWorks2017
Go

SELECT [FirstName] + [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Adam'
UNION
SELECT [FirstName] + [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Ben'
ORDER BY [LastName]

If you don’t want the extra column in your output, you can put the SELECT statement with the UNION in a sub-query, as follows:

Use AdventureWorks2017
Go

SELECT [FullName] 
FROM (
SELECT [FirstName] +' '+ [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Adam'
UNION
SELECT [FirstName] +' '+ [LastName] AS [FullName],LastName
FROM Person.Person
where FirstName='Ben'
) A
ORDER BY [LastName]