How to get strings comma separated in ascending order with SQL Server's STRING_AGG()

Question

My table looks like this

Color  Order 
------------
Red    49
Blue   32
Green  80
Green  30
Blue   93
Blue   77
Red    20
Green  54
Red    59
Red    42
Red    81
Green  35
Blue   91

My Query is

SELECT Color, Count(*) AS Count, STRING_AGG(Order,',') AS AggOrder
FROM MyTable
GROUP BY Color

When I group by Color and aggregate I get un-sorted orders

Something like this

Color  Count  AggOrder
------------------------------
Red    5      49,20,59,42,81
Blue   4      32,93,77,91
Green  4      80,30,54,35

Problem : AggOrder is un-ordered 49,20,59,42,81

I want to order it

so the end result is

Color  Count  AggOrder
------------------------------
Red    5      20,42,49,59,81
Blue   4      32,77,91,93
Green  4      30,35,54,80

I tried this query

SELECT Color, Count(*) AS Count, STRING_AGG(Order,',') AS AggOrder
FROM MyTable
GROUP BY Color
ORDER BY Order

But this gives an error.

Any idea how to fix that?

Answer 1

You can use within group syntax

SELECT Color
    , Count(*) AS Count
    , STRING_AGG([Order],',') WITHIN GROUP (ORDER BY [Order]) AS AggOrder
FROM MyTable
GROUP BY Color