October 23, 2014 at 6:36 am
set @was = '--2--'
set @becomes = '--2--'
, @becomes = @becomes + '|'+ becomes
from
wasbecomestable
order by DATALENGTH(was) desc -- total different result if this part is not used.
print ''
print @was
print @becomes
print 'Conclusion: This is not the complete list'
In the above code adding / removing the order by clause gives a completely different result. I want to influence the order the items are added to a string parameter.
I use this or similar constructions fairly often, have not encountered this before, and a lot of my 'generating' code depends on similar constructions.
I am missing something ?
I am using an illegal construction ?
Is this a bug ?
Question is why does the ORDER BY result in only one occurrence in the string ?
See the code below, which containes a complete sample.
Thanks for your time and attention,
Ben
-- ben brugman
-- 20141023
--
-- Sample code.
-- I want to create a string where the values of a column are added and are seperated with a vertical bar.
-- I want to have the items ordered with their length, longest first.
--
-- Problem when using a ORDER BY clause, only a single item gets added to the string.
--
CREATE TABLE [dbo].[wasbecomestable](
[GROEP] [varchar](300) NULL,
[was] [varchar](300) NULL,
[becomes] [varchar](300) NULL
) ON [PRIMARY]
set nocount on
INSERT INTO [wasbecomestable] ([GROEP],[was],[becomes])VALUES('1','ANTON','AAAA')
INSERT INTO [wasbecomestable] ([GROEP],[was],[becomes])VALUES('1','BERT','BBBB')
INSERT INTO [wasbecomestable] ([GROEP],[was],[becomes])VALUES('1','DIRK','DDDD')
INSERT INTO [wasbecomestable] ([GROEP],[was],[becomes])VALUES('1','EDUARD','EEEE')
INSERT INTO [wasbecomestable] ([GROEP],[was],[becomes])VALUES('1','RUUD','RRRR')
INSERT INTO [wasbecomestable] ([GROEP],[was],[becomes])VALUES('1','WHO','MASTER')
set nocount off
-- select * from wasbecomestable
------------------------------------------------------------------------------------
SELECT '-- SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
-- SQL Server 10.50.2550.0 - SP1 (Developer Edition (64-bit))
------------------------------------------------------------------------------------
declare @was varchar(4000) = '--1--'
declare @becomes varchar(4000) = '--1--'
, @becomes = @becomes + '|'+ becomes
from
wasbecomestable
print ''
print @was
print @becomes
print 'Conclusion: this is not the desired order'
--1--|ANTON|BERT|DIRK|EDUARD|RUUD|WHO
--1--|AAAA|BBBB|DDDD|EEEE|RRRR|MASTER
------------------------------------------------------------------------------------
set @was = '--2--'
set @becomes = '--2--'
, @becomes = @becomes + '|'+ becomes
from
wasbecomestable
order by DATALENGTH(was) desc
print ''
print @was
print @becomes
print 'Conclusion: This is not the complete list'
--2--|WHO
--2--|MASTER
------------------------------------------------------------------------------------
select * into ##WW from wasbecomestable
print ''
set @was = '--3--'
set @becomes = '--3--'
, @becomes = @becomes + '|'+ becomes
from
##WW
print @was
print @becomes
print 'Conclusion: this is not the desired order'
--3--|ANTON|BERT|DIRK|EDUARD|RUUD|WHO
--3--|AAAA|BBBB|DDDD|EEEE|RRRR|MASTER
drop table ##WW
------------------------------------------------------------------------------------
IF exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'wasbecomestable') drop table wasbecomestable
October 23, 2014 at 7:44 am
This is interesting. If you order by a column name then the expected result is returned. If you order by an ordinal position, 1 or 2, you get an error squiggle underneath the expression but the query still runs. You can order by expressions such as GETDATE() and RAND() and the query will work because these expressions are evaluated once per query. If you order by an expression which is evaluated per row - LEFT(), NEWID(), LEN(), DATALENGTH() - then only the last row of the ordered set, ordered by the expression chosen, is shown in the result (guessing for NEWID() of course).
The (in)famous Quirky Update indicates that setting the value of variables within a QU query occurs in the order of the clustered index (or the order of the rows in a heap), or in other words "in logical storage order of the rows in the table". The method relies on this fact to work. I'm not hugely surprised that something goes wrong with a similar SELECT if you muck about with the order.
If you need the results in a particular order then you could persist the expression result in the table.
FOR XML PATH() is the recommended approach for this type of concatenation, so you have another way out.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 23, 2014 at 9:12 am
First of all thanks,
Now I see I did muck up a bit, the temp table ##WW should have been ordered, see the code below. Probably lost that part of the statement cleaning up the example. Sorry.
The conclusion becomes now the order is correct.
(I thought I had a correct solution, but saw that the sample was not correct, did not notice that the Ordering had disappeared).
But I have learned not to depend on "in logical storage order of the rows in the table". So I actually want to avoid that construction.
ChrisM@Work (10/23/2014)
This is interesting. If you order by a column name then the expected result is returned. If you order by an ordinal position, 1 or 2, you get an error squiggle underneath the expression but the query still runs. You can order by expressions such as GETDATE() and RAND() and the query will work because these expressions are evaluated once per query. If you order by an expression which is evaluated per row - LEFT(), NEWID(), LEN(), DATALENGTH() - then only the last row of the ordered set, ordered by the expression chosen, is shown in the result (guessing for NEWID() of course).
I see that 1 or 2 for the ordering only gives a single item.
I do not see where the ordinal position comes from. (When using information_schema, I think I often use the ordinal position with succes). In this example is there an ordinal position?
The bold part. Why? Should I have known this effect? (Am I using illegal constructs?)
The (in)famous Quirky Update indicates that setting the value of variables within a QU query occurs in the order of the clustered index (or the order of the rows in a heap), or in other words "in logical storage order of the rows in the table". The method relies on this fact to work. I'm not hugely surprised that something goes wrong with a similar SELECT if you muck about with the order.
Could you point out '(in)famous Quirky Update'
(Did google for this, saw a lot of examples but not a 'generic' description or example.)
If you need the results in a particular order then you could persist the expression result in the table.
See the example below, during cleaning of the sample this got lost in --3--.
FOR XML PATH() is the recommended approach for this type of concatenation, so you have another way out.
Could you show me how?
(Sorry I am not to familiar with the 'FOR XML PATH()' construction. Partly because I do not consider this to be SQL language).
Thanks for your time and anwser,
Ben
------------------------------------------------------------------------------------
select * into ##WW2 from wasbecomestable order by DATALENGTH(WAS) desc
----------------------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
----------------------------------------Lost in the sample / Sorry
----------------------------------------------------------------------
print ''
set @was = '--4--'
set @becomes = '--4--'
, @becomes = @becomes + '|'+ becomes
from
##WW2
print @was
print @becomes
print 'Conclusion: This is in the correct order.'
--4--|EDUARD|ANTON|BERT|DIRK|RUUD|WHO
--4--|EEEE|AAAA|BBBB|DDDD|RRRR|MASTER
drop table ##WW2
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- Use a correct tablename for example 'wasbecomestable'
-- Order by Ordinal_position works.
--
print ''
set @was = '--5--'
set @becomes = '--5--'
select @was = @was+'|'+column_name
, @becomes = @becomes + '|'+data_type
from
information_schema.COLUMNS where TABLE_NAME = 'wasbecomestable'
order by ordinal_position
print @was
print @becomes
--5--|GROEP|was|becomes
--5--|varchar|varchar|varchar
print 'Conclusion: This is in the correct order.'
------------------------------------------------------------------------------------
October 24, 2014 at 6:51 am
Hi Ben
ben.brugman (10/23/2014)
First of all thanks,Now I see I did muck up a bit, the temp table ##WW should have been ordered, see the code below. Probably lost that part of the statement cleaning up the example. Sorry.
The conclusion becomes now the order is correct.
(I thought I had a correct solution, but saw that the sample was not correct, did not notice that the Ordering had disappeared).
But I have learned not to depend on "in logical storage order of the rows in the table". So I actually want to avoid that construction.
ChrisM@Work (10/23/2014)
This is interesting. If you order by a column name then the expected result is returned. If you order by an ordinal position, 1 or 2, you get an error squiggle underneath the expression but the query still runs. You can order by expressions such as GETDATE() and RAND() and the query will work because these expressions are evaluated once per query. If you order by an expression which is evaluated per row - LEFT(), NEWID(), LEN(), DATALENGTH() - then only the last row of the ordered set, ordered by the expression chosen, is shown in the result (guessing for NEWID() of course).I see that 1 or 2 for the ordering only gives a single item.
I do not see where the ordinal position comes from. (When using information_schema, I think I often use the ordinal position with succes). In this example is there an ordinal position?
Ordinal position as in ORDER BY 1, 2, 3
The bold part. Why? Should I have known this effect? (Am I using illegal constructs?)
The (in)famous Quirky Update indicates that setting the value of variables within a QU query occurs in the order of the clustered index (or the order of the rows in a heap), or in other words "in logical storage order of the rows in the table". The method relies on this fact to work. I'm not hugely surprised that something goes wrong with a similar SELECT if you muck about with the order.
Could you point out '(in)famous Quirky Update' [/QUOTE]
Sure: this article by Jeff Moden[/url] is the best reference.
(Did google for this, saw a lot of examples but not a 'generic' description or example.)
If you need the results in a particular order then you could persist the expression result in the table.
See the example below, during cleaning of the sample this got lost in --3--.
FOR XML PATH() is the recommended approach for this type of concatenation, so you have another way out.
Could you show me how?
(Sorry I am not to familiar with the 'FOR XML PATH()' construction. Partly because I do not consider this to be SQL language).
No problem. Try this Simple Talk article[/url].
Thanks for your time and anwser,
Ben
------------------------------------------------------------------------------------
select * into ##WW2 from wasbecomestable order by DATALENGTH(WAS) desc
----------------------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
----------------------------------------Lost in the sample / Sorry
----------------------------------------------------------------------
print ''
set @was = '--4--'
set @becomes = '--4--'
, @becomes = @becomes + '|'+ becomes
from
##WW2
print @was
print @becomes
print 'Conclusion: This is in the correct order.'
--4--|EDUARD|ANTON|BERT|DIRK|RUUD|WHO
--4--|EEEE|AAAA|BBBB|DDDD|RRRR|MASTER
drop table ##WW2
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- Use a correct tablename for example 'wasbecomestable'
-- Order by Ordinal_position works.
--
print ''
set @was = '--5--'
set @becomes = '--5--'
select @was = @was+'|'+column_name
, @becomes = @becomes + '|'+data_type
from
information_schema.COLUMNS where TABLE_NAME = 'wasbecomestable'
order by ordinal_position
print @was
print @becomes
--5--|GROEP|was|becomes
--5--|varchar|varchar|varchar
print 'Conclusion: This is in the correct order.'
------------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply