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.

Composite vs Single Primary Key

Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?

I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?

First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:

Create Table T(p int primary key nonclustered, x int, y int);

Create Clustered Index T_i on T(x);

Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.

Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.

|||

2nf just states that all data in a table must rely on the entire key.

that doesnt mean the key cant be composite.

if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)