Cannot use order by clause for correct xml path (Sql server)

Question

I have the following situation (say tblRecord)

ID RowNum Data 1 1 and seventy nine 1 2 five hundred 1 3 two thousand 

I need a conclusion

 ID Data 1 two thousand five hundred and seventy nine 

I have the following request

 select ID , Data = ( Select ' ' + cast(Data as varchar(max)) from tblRecord t2 where t2.RowNum= t1.RowNum and t2.ID =t1.ID order by t1.RowNum for xml path('')) from tblRecord t1 group by t1.ID 

But the conclusion

 ID Data 1 five hundred two thousand and seventy nine 

Help needed for this.

thanks

Answer 1

Try the following:

 SELECT DISTINCT ID, Data = (SELECT ' ' + Data FROM dbo.tblRecord t2 WHERE t2.ID = t1.ID ORDER BY t2.RowNum DESC FOR XML PATH('') ) FROM dbo.tblRecrd t1 

Your first problem was ORDER BY t1.RowNum in the internal selection - instead, you need ORDER BY t2.RowNum . Secondly: this is a join condition where t2.RowNum= t1.RowNum not necessary and causes problems. And third: GROUP BY again unnecessary and unnecessary - just use SELECT DISTINCT to achieve what you are looking for.

Also - you don’t know why you chose Data as VARCHAR (MAX) ???