How to order STRING_AGG

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?

https://stackoverflow.com/questions/66864207/how-to-order-string-agg March 30, 2021 at 10:58AM