Select STUFF with Order By

Transact-SQL
,
1

I've been spinning my wheels with this one.
It needs an order by in the stuff.
Tried to add group by after taking out the distinct but no luck.
It needs an ORDER BY a.Sequence
a is the ServiceInvoiceApprovers table.

SELECT
    d.Name,        

    (SELECT
    STUFF((SELECT ' ' + '(' + loc.LocationCode + ') ' + p.FirstName + ' ' + p.LastName  + ', ' 
    FROM ServiceInvoiceApprovers a
    INNER JOIN tblServiceDefLocation l ON l.LocationID = a.LocationID
    INNER JOIN tblParty p ON p.PartyID = a.EmployeeID
    INNER JOIN tblLocation loc ON loc.LocationID = l.LocationID
    WHERE (a.ServiceDefID = d.ServiceDefID
    AND a.ServiceTypeID = d.ServiceTypeID
    AND a.LocationID = l.LocationID
    -- ORDER BY a.Sequence
    )
    FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')) 
    AS Approvers

FROM tblServiceDef d

Any suggestions?

Thanks!

2

What you have shown should work if you uncomment the ORDER BY and also move it outside of the brackets for WHERE clause. What is the error that you are getting? Or is it not ordering at all?

SELECT  d.Name ,
        ( SELECT    STUFF((SELECT   ' ' + '(' + loc.LocationCode + ') '
                                    + p.FirstName + ' ' + p.LastName + ', '
                           FROM     ServiceInvoiceApprovers a
                                    INNER JOIN tblServiceDefLocation l ON l.LocationID = a.LocationID
                                    INNER JOIN tblParty p ON p.PartyID = a.EmployeeID
                                    INNER JOIN tblLocation loc ON loc.LocationID = l.LocationID
                           WHERE    ( a.ServiceDefID = d.ServiceDefID
                                      AND a.ServiceTypeID = d.ServiceTypeID
                                      AND a.LocationID = l.LocationID
                                    )
                          ORDER BY a.Sequence
                    FOR   XML PATH('') ,
                              TYPE).value('.', 'VARCHAR(max)'), 1, 1, '')
        ) AS Approvers
FROM    tblServiceDef d;
3

Thanks. I was putting it in the wrong place.