ORDER BY items must appear in the select list if the statement contains a UNION operator.,sql,server,2005,asp.net,Union,select,insert,query,order by

If you are using UNION Operator when cobining the results of multiple select statement and in the select statement theire is ORDER BY clause. And the clumns in the order by are not part of Select clause theire is chance to come the following error.

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.

Suppose you have two tables called staff and teachers both tables have firstname and lastname and now you wants to concatenate two tables to generate fullname and you are sorting by firstname as below:

SELECT [FirstName] + [LastName] AS [FullName]
FROM [dbo].[staff]
UNION
SELECT [FirstName] + [LastName] AS [FullName]
FROM [dbo].[teachers]
ORDER BY [FirstName]

Since the firstname column is not part of the output list, 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.

If you wants to avoid these error you can do it in two ways.

1. You can avoid the sort caluse from the select statement.

2. If you really needs this sort clause you can include the sort column in the select list as below


SELECT [FirstName] + [LastName] AS [FullName],[FirstName]
FROM [dbo].[staff]
UNION
SELECT [FirstName] + [LastName] AS [FullName],[FirstName]
FROM [dbo].[teachers]
ORDER BY [FirstName]





Happy Programming