In Variable concatenation and order by filters results (like where condition) - by rgrus

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 383641 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/24/2008 5:12:37 AM
Access Restriction Public


I want to do a simple concatenation in a variable.  With no order by or with the order by set on the column I want to concatenate, everything works fine.  However as soon as I add another column in the sort list, only the first row is returned (or saved) in the variable.
Sign in to post a comment.
Posted by Umachandar [MSFT] on 12/1/2008 at 11:38 AM
Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if there are workarounds. See the below KB article for more details:

The ONLY guaranteed mechanism are the following:

1. Use cursor to loop through the rows in specific order and concatenate the values
2. Use for xml query with ORDER BY to generate the concatenated values
3. Use CLR aggregate (this will not work with ORDER BY clause)

Umachandar, SQL Programmability Team
Posted by rgrus on 11/24/2008 at 6:10 AM
I posted this thread on and other workarounds were figured out by Edwards Elliot :

Using select top 100 percent fails, but using top 99.9% works. The only problem with that is that it can eventually filter out results if the dataset is big enough. I then came up with an alternative... select top 999999999...

Assuming you know that dataset can never exceed that amount (500 rows in my case), then you're safe. However I would much rather have the bug fixed than using a hack :P.