Unable to use order by clause with for xml path properly(Sql server)

sql-server-2005

Solution

Try this:

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 the `ORDER BY t1.RowNum` in the inner select - needs to be `ORDER BY t2.RowNum` instead. Secondly: this join condition `where t2.RowNum= t1.RowNum` is not necessary and causes problems. And thirdly: the `GROUP BY` is again neither needed nor helpful - just use the `SELECT DISTINCT` to achieve what you're looking for.

Also - not sure why you're casting `Data` as VARCHAR(MAX) ???

Problem

I have the below situation (say tblRecord) ``` ID RowNum Data 1 1 and seventy nine 1 2 five hundred 1 3 two thousand ``` I need the output to be ``` ID Data 1 two thousand five hundred and seventy nine ``` I have the below query ``` 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 output is ``` ID Data 1 five hundred two thousand and seventy nine ``` Help needed for this. Thanks

Original source