sql - How do I order by a column in a subquery with a distinct clause? -

i have extremely large query need include comma delimited list in. i'm accomplishing subqueries so:

        stuff(( select  distinct ',' + t1.name                    t2                         inner join t1   on t1.id        = t2.id                   t2.otherfield = 12345                 order t2.id                 xml path(''), type).value('.','varchar(max)'), 1, 1, '') talentname 

in particular case need add distinct clause (as shown). however, when following error:

    msg 145, level 15, state 1, line 2     order items must appear in select list if select distinct specified. 

i understand why error occurs, i'm not sure it. t2.id cannot included in select statement returned part of resultset. cannot remove order clause because comma delimited list must in order matches list i'm generating of ids.

how can insure comma delimited list both distinct , in proper order?

end goal

to clarify i'm trying accomplish, query pull comma delimited lists of both t1.name , t1.id. i'm doing in 2 separate stuff statements, if there alternate method i'd open it. query return several thousand rows, i'm attempting find set-based solution avoid thousands of ad-hoc queries running each time our web page loaded.

you can use derived table group by below. t1.id included in group on assumption pk of table1 correctly distinguish different individuals same name.

 select stuff(     (     select ',' + t1name   (select t1.name    t1name,                min(t2.id) t2id           t2                inner join t1                  on t1.id = t2.id          t2.otherfield = 12345         group  t1.id,                   t1.name) x order  t2id xml path('')   , type).value('.','varchar(max)')                                , 1, 1, '') talentname