mardi 21 juillet 2015

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

I have an extremely large query that I need to include a comma delimited list in. I'm accomplishing this with subqueries like so:

        STUFF(( SELECT  distinct ',' + t1.Name
                FROM    t2
                        inner join t1   ON t1.ID        = t2.ID
                WHERE   t2.otherField = 12345
                ORDER BY t2.ID
                FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') as talentName

In this particular case I need to add a distinct clause (as shown). However, when I do so I get the following error:

    Msg 145, Level 15, State 1, Line 2
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I understand why this error occurs, but I'm not sure what to do about it. t2.ID cannot be included in the select statement as it would then get returned as part of the resultset. I cannot remove the order by clause because the comma delimited list must be in an order that matches another list I'm generating of the IDs.

How can I insure that this comma delimited list is both distinct and in the proper order?

End Goal

To help clarify what I'm trying to accomplish, this query will pull comma delimited lists of both the t1.name and t1.ID. I'm currently doing this in two separate STUFF statements, but if there is an alternate method I'd be open to it. This query will return several thousand rows, so I'm attempting to find a set-based solution to avoid thousands of ad-hoc queries running each time our web page is loaded.

Aucun commentaire:

Enregistrer un commentaire