SQL Server Error Messages - Msg 104
Error Message
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.
Causes
This error happens when you are using the UNION operator when combining the
results of multiple SELECT statements and you also specified an ORDER BY clause
where the columns in the ORDER BY clause are not part of the columns in your
SELECT list.
To illustrate, suppose you have 2 tables that contain the employees and managers
in a company. Both tables have a 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:
SELECT [FirstName] + [LastName] AS [FullName]
FROM [dbo].[Employees]
UNION
SELECT [FirstName] + [LastName] AS [FullName]
FROM [dbo].[Managers]
ORDER BY [LastName]
Since the LastName column is not part of the output, although it’s part of one
of the columns in the SELECT list, the following error will be encountered:
Server: Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains a UNION operator.