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.

Solution / Work Around :

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.

SELECT [FirstName] + [LastName] AS [FullName], [LastName]
FROM [dbo].[Employees]
UNION
SELECT [FirstName] + [LastName] AS [FullName], [LastName]
FROM [dbo].[Managers]
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:

SELECT [FullName] 
FROM (
SELECT [FirstName] + [LastName] AS [FullName], [LastName]
FROM [dbo].[Employees]
UNION
SELECT [FirstName] + [LastName] AS [FullName], [LastName]
FROM [dbo].[Managers]) A
ORDER BY [LastName]