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

Question

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

Answer 1

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) ???