Ordering contents of T-SQL STRING_AGG()

Question

I need help!

I have this query

SELECT  TOP 100 PERCENT * 
FROM    [dbo].[Conversion_imageTiles]
WHERE   [old_file] = 'tileWeir-E-W-U-D.gif'
ORDER   BY [old_file],[static_class]

It produces these results..

Notice the field [static_class] is in alphabetical order....

But this is a sub query...

SELECT   [old_file]
         ,STRING_AGG(CONCAT([static_class],' ',[rotation]),' ')[static_class]
FROM    (
        SELECT  TOP 100 PERCENT * 
        FROM    [dbo].[Conversion_imageTiles]
        WHERE   [old_file] = 'tileWeir-E-W-U-D.gif'
        ORDER   BY [old_file],[static_class]
) a
WHERE [old_file] = 'tileWeir-E-W-U-D.gif'
GROUP   BY [old_file]
ORDER BY [old_file],[static_class]

See, there it is in the middle - popping out a result set that has [static_class] in alphabetical order.. So why are the results of the full query this..

Notice [static_class] is backwards, I would expect it to start with 'bypass-weir..' Changing the ORDER clauses has no effect... its ALWAYS in that order...

Help!

EDIT: I wondered if it was due to the internal ordering based off the [id] column in the internal sub query.

Removing it:

SELECT TOP 100 PERCENT [old_file],[static_class],[rotation] 
FROM   [dbo].[Conversion_imageTiles]
WHERE  [old_file] = 'tileWeir-E-W-U-D.gif'
ORDER  BY [old_file],[static_class]

Had no effect

Refer to [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) use `WITHIN GROUP ( ORDER BY ...)` — Squirrel, Sep 23 '21 at 14:21
[Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Sep 23 '21 at 14:24
My bad - replaced images of code with actual code blocks instead. — Morvael, Sep 23 '21 at 14:31
@Squirrel WITHIN GROUP(), is the answer. If you want to post it as an answer I will mark it as such, — Morvael, Sep 23 '21 at 14:37

Ordering contents of T-SQL STRING_AGG()

0 Answers0