Ordered Concatenation (solved)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Ordered Concatenation (solved)

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 12:21:42

Hopefully, I'm having a brain issue today, but maybe another set of eyes can help me out. (Additionally, I've seen similar issues popping up all over the place on forums).

As we all know you cannot add an ORDER BY clause to an inline view or derived table without specifying TOP or FOR XML. The issue is that when you do specify TOP sql seems to ignore the ORDER BY. I was messing with Fun With numbers that I came across ([url]http://www.sql-server-performance.com/vk_fun_numbers_transactsql.asp[/url]) and noticed that it didn’t work right, at least in SQL 2005.

-- Set up
SET NOCOUNT ON
DECLARE @Numbers TABLE (Number SMALLINT IDENTITY(1, 1) PRIMARY KEY)

WHILE 1 = 1
BEGIN
	INSERT INTO @Numbers DEFAULT VALUES

	IF SCOPE_IDENTITY() = 500 
	BEGIN
		BREAK
	END
END

DECLARE @input VARCHAR(100), @output VARCHAR(100), @len SMALLINT
SET @input = 'CDBEA'
SET @output = ''
SET @len = LEN(@input)

-- Works as Expected.
SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val

-- Not what is expected.
SELECT *
FROM
(
	SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
	FROM dbo.Numbers (NOLOCK)
	WHERE Number <= @len
	ORDER BY Val
) AS Derived

I realize that a derived table is basically an unordered set, so ordering it like this goes against the whole view/set thing. However, it used to work on SQL 2000. So, my question is: how do you duplicate that functionality in 2005 so that you can do ordered string concatenation like this: (which is the goal of my exercise)

SELECT @output = @output + Val 
FROM 
(
	SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
	FROM dbo.Numbers (NOLOCK)
	WHERE Number <= @len
	ORDER BY Val
) AS Derived

SELECT @input AS 'Original string', @output AS 'Sorted string'

It is probably simple and I'm just still asleep or something.

Thaks,
-Ryan

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-11 : 13:01:10

http://msdn2.microsoft.com/en-us/library/ms188385.aspx

BOL: When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Since the outer query does not contain an Order by the sort order of the result is undefined.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 13:24:34

quote:
Originally posted by cvraghu

http://msdn2.microsoft.com/en-us/library/ms188385.aspx

BOL: When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Since the outer query does not contain an Order by the sort order of the result is undefined.

Thanks, but I mentioned above that I already know that. The point of the exercise is to figure out how to do an ordered concatenation.

There is this other thread which gets around the ORDER BY by using the RANK() function: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81935[/url].

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 13:59:30

Bah, I figured it out using one Peter Larsson's methods:

SELECT @output = STUFF((SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@input, Number, 1) FROM @Numbers ORDER BY SUBSTRING(@input, Number, 1) FOR XML PATH('')), 1, 0, '')

Thanks Peter! :)

Still is interesting to me why the other methods do not work when you add the SUBSTRING..

Cheers,

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 15:19:27

Thanks.
No need for stuff here since there is no delimiter.

SELECT @Output = (SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@Input, Number, 1) FROM @Numbers ORDER BY 1 FOR XML PATH(''))

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources