SELECT @t = @t + @comma + a.a
 ,@comma = ','
 FROM #a a
 ORDER BY 2

rajesh.subramanian (6/25/2010)

---

Good Question.

SELECT @t = @t + @comma + a.a
,@comma = ','
FROM #a a
ORDER BY 1

Result : C

SELECT @t = @t + @comma + a.a
,@comma = ','
FROM #a a
ORDER BY a.a

Result : A,A,B,B,C,C

changing the order by giving different result...experts please explain?

rajesh.subramanian (6/25/2010)

---

Good Question.

SELECT @t = @t + @comma + a.a
,@comma = ','
FROM #a a
ORDER BY 1

Result : C

SELECT @t = @t + @comma + a.a
,@comma = ','
FROM #a a
ORDER BY a.a

Result : A,A,B,B,C,C

changing the order by giving different result...experts please explain?

ziangij (6/25/2010)

---

since there is only one column, why is this working as well ?

 SELECT @t = @t + @comma + a.a
 ,@comma = ','
 FROM #a a
 ORDER BY 2

Result : A,A,B,B,C,C

Christian Buettner-167247 (6/25/2010)

---

There are two "columns", the second column is "@comma".
SQL Server "optimizes" this trivial ORDER BY by removing it completely. (If you think about it, you are ordering by a static value in this case which is the same for all rows). You can see this in the execution plan (there is no order by in the execution plan)
Now try the following instead, and you will see the sorting is included in the plan:
ORDER BY @comma + REPLACE(a,a,'')