|
|
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/Workaround:
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]
|