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.'
------------------------------------------------------------------------------------