August 28, 2008 at 7:28 am
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
August 28, 2008 at 7:49 am
If you look at the execution plan you can see that in the query using ORDER BY 1 the SORT takes place AFTER the Compute Scalar operation, which I assume is the variable assignment while in the other queries the SORT occurs BEFORE the Compute Scalar. This is apparently why the results are different.
I also recall reading somewhere, and I don't recall where, that using column ordinal in the ORDER BY is not recommended.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply