Wednesday, August 19, 2009

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

3 comments:

  1. really tricky.
    But what if this error occurs even not having a SORT BY clause?
    Well my SELECT Statements are quite complex and work fine (with my MS SQL 200 Server). But when I connect them with the UNION clause then the error appears.
    Regards Fritz

    ReplyDelete
  2. mine too.

    is there a compatibility setting in 2008?

    would be nice to flip a switch instead of updating all the queries.

    ReplyDelete
  3. Kristen's answer at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54082
    seems like the best solution I've seen

    ReplyDelete