Try this in SQL 2005:
CREATE TABLE #TAB (MY_FIELD varchar(10), a int)
INSERT INTO #TAB VALUES ('test1', 1)
INSERT INTO #TAB VALUES ('test2',2)
INSERT INTO #TAB VALUES ('test3',3)
DECLARE @val VARCHAR(2000)
SELECT MY_FIELD FROM #TAB
--1
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
SELECT @val --correct
--2
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY 1 --WRONG
SELECT @val
--3
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY my_field --correct
SELECT @val
--4
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY a --correct
SELECT @val
DROP TABLE #TAB
We have had several major threads here on stuff like running totals that relied on this 'trick' to work. I was pretty surprised though that this simple example seems to output only the final value when the order by 1 is in place.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service