order by, while assigning columns to a value

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 2000 Forums

SQL Server Development (2000)

order by, while assigning columns to a value

Author

Topic

returnofthemack
Starting Member

16 Posts

Posted - 2003-08-08 : 04:37:03

I would like to assign a string of values to a variable, but when using the order by clause, it appears to only take the last variable. Keep in mind that the examples I'll be showing here are simplified examples. I'm also aware that I can accomplish the task using temp tables, etc., but am trying to accomplish this using a single query.

I have a table called 'orderproducts' with two columns, orderid and grouping id, and the table contains 4 rows. these columns hold integer values. I am trying to generate a list of grouping ids, but ordered by orderid. The following query WORKS without the order by clause:

declare @x varchar(200)
select @x=isnull(@x+',','')+cast(orderid as varchar(10))
from orderproducts
--order by orderid --> this line has been commented out
print @x

the result is: 10092,10092,10092,10092,10092 because all four rows/products are from the same order - order 10092. However, when I execute it with the order by clause, I get the following result: 10092. That's it. Only one instance of the orderid.

Also, when I execute the query and ordering by groupid, it also WORKS. Here is the query:

declare @x varchar(200)
select @x=isnull(@x+',','')+cast(orderid as varchar(10))
from orderproducts
order by groupingid
print @x

and the result: 10092,10092,10092,10092,10092

This should be a simple query, but it has stumped me. Do any of you know why this is happening?

Thanks in advance for your help...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 05:03:30

create table #a (i int, j int)
insert #a select 1,2
insert #a select 1,2
insert #a select 1,2
insert #a select 1,2
insert #a select 2,1

declare @s varchar(1000)
select @s = coalesce(@s,'') + convert(varchar(10),i)
from #a
order by i
select @s

11112

I suspect it is something to do with an index and the optimiser is being fooled into thinking it can just use the distinct values from the index.
I can't reproduce it using a temp table so will need more info about your structure.
Can you post it with a temp table create and populate as above.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-08 : 07:50:28

Does

declare @x varchar(200)
select @x=isnull(@x+',','')+cast(orderid as varchar(10))
from orderproducts
order by groupingid, orderid

help ??

- Jeff

returnofthemack
Starting Member

16 Posts

Posted - 2003-08-09 : 03:58:23

I have no indexes on the table at this point. The only thing that has been added to the table is a primary key on groupingid.

I tried your table/query and it worked fine. It has something to do with my table, but can't seem to see anything wrong.

Also, ordering by groupingid then orderid will not necessarily order the records in the order I want.

Any other ideas?

returnofthemack
Starting Member

16 Posts

Posted - 2003-08-09 : 04:26:32

yes, it was something wrong with the table. dropped and recreated it and what do you know, it fixed the problem.

thanks for your help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-09 : 11:19:21

quote:

Also, ordering by groupingid then orderid will not necessarily order the records in the order I want.

Then how do you want it ordered? if it is not specified, it will be completely random. You should always explicitly declare how you wish the results to be ordered, or SQL can return the results (and build your strings) in any order it sees fit.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-09 : 11:27:33

I've seen the same behavior when concatenating string variables, using ORDER BY sometimes made it even worse. If you really need that order, create a clustered index on the (temp) table on the item(s) you want ordered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources