Concatenate field based on unique id. (Follow up)

thanks for your earlier reply.

If i want to order the IDs by a Timestamp column in descending order how do i do it. I couldn't do it in Inner query.
right now it gives in random order. Is there any other way to get it?

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, createTS, count(*) as seq
from your_table as t1 join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment, createTS
order by createTS desc -> gives error
) as t3
group by t3.id;

Got it working. I used 'top 100 percent" and used the ORDER BY in inner query.
Thanks.|||Note that using TOP 100 PERCENT is still not guaranteed to work. It depends on the query plan and even more so in SQL Server 2005. The use of ORDER BY clause is specific to a scope only and in your example to the derived table. Generally, you should not rely on the order in which the rows are processed in a SELECT statement. You should basically consider a SELECT statement source as an unordered set of rows. In your example, you can achieve the results by changing the condition:

t2.Comment <= t1.Comment

to

t2.CreateTs <= t1.CreateTs

This assumes that time stamp value is unique per id and this would guarantee that the sequence number is based on sorting the values in ascending order. You can incorporate additional conditions to handle matching time stamps and different comments. As I said before, doing these type of operations in SQL is not the right approach. These can be done very easily on the client side and with less work / assumptions.