Article ID: 287515 - View products that this article applies to.
This article was previously published under Q287515
You may encounter unexpected results when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries. The results of aggregate concatenation queries in Microsoft SQL Server depend upon whether or not Transact-SQL expressions are applied to the columns in the ORDER BY clause of the query. For more information or to see an example of a aggregate concatenation query and the behavior exhibited, refer to the "More Information" section of this article.
The correct behavior for an aggregate concatenation query is undefined.
An examination of the SHOWPLAN output of the query reveals that the SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans.
The ANSI SQL-92 specification requires that any column referenced by an ORDER BY clause match the result set, defined by the columns present in the SELECT list. When an expression is applied to a member of an ORDER BY clause, that resulting column is not exposed in the SELECT list, resulting in undefined behavior.
Additionally, the ANSI specifications regarding how an ORDER BY clause is to be applied state that the effect of the ORDER BY should be the same as if you take the entire result set produced by the SELECT list, and then perform the ordering of the table based on those columns in the SELECT list.
In order to achieve the expected results from an aggregate concatenation query, apply any Transact-SQL function or expression to the columns in the SELECT list rather than in the ORDER BY clause.
An aggregate concatenation query is a query that combines the values of multiple rows into one row.
Steps to Reproduce BehaviorUse the following Transact-SQL script to reproduce the behavior.
Note the application of the LTRIM and RTRIM functions to the [C1] column in the ORDER BY clause versus the SELECT list.
Article ID: 287515 - Last Review: October 16, 2003 - Revision: 3.2